---

---

  

Oracle檢視執行計劃的幾種方法：http://blog.itpub.net/26736162/viewspace-2136865/

 

---

---

一、  如何得到真實的執行計劃？

在Oracle資料庫中判斷得到的執行計劃是否準確，就是看目標SQL是否被真正執行過，真正執行過的SQL所對應的執行計劃就是準確的，反之則有可能不準，因此，通過10046事件及如下的幾種方式得到的執行計劃是最準確的，而從其它方式獲取到的執行計劃都有可能不準確。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS'));

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER, 'ADVANCED ALLSTATS'));

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));

這裡需要注意的是，雖然SQL*Plus的AUTOTRACE功能有部分是真實執行了SQL語句的（例如所有DML語句），但是，由於該命令所顯示的執行計劃來源於呼叫EXPLAIN PLAN命令，所以，其得到的執行計劃依然可能不準確（特別是在使用了繫結變數的情況下）。那麼，為什麼EXPLAIN PLAN命令裡顯示的預估執行計劃與該SQL真實的執行計劃不一樣呢？原因有多個方面，常見的情況包括以下幾個方面：

①　繫結變數窺視（Bind Peeking）：EXPLAIN PLAN裡不會進行繫結變數窺視，但是Runtime Plan裡會進行繫結變數窺視，所以，如果發生這種情況，那麼會使這兩個執行計劃產生差異。

②　隱式轉換：Explain Plan裡不會考慮繫結變數的型別，但是Runtime Plan裡會考慮型別，從而有可能會根據繫結變數的型別出現隱式轉換，所以謂詞（Predicate）會發生變化，使得執行計劃也會產生差異。

③　優化器引數：執行Explain Plan的Session與Runtime Plan的Session不是同一個。如果各個Session之間存在優化器引數差異，那麼執行計劃也會產生差異。

④　統計資訊收集引數：Explain Plan始終是用最新的統計資訊產生執行計劃，但是，Runtime Plan不一定會用最新的統計資訊。因此也會產生執行計劃差異。在收集統計資訊時，一個與快取的遊標是否失效的很重要的引數為NO_INVALIDATE。在重新收集統計資訊時，可以指定NO_INVALIDATE選項。該選項有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE這3個值。如果取值為TRUE，那麼表示收集統計資訊後不進行遊標失效動作，原有的Shared Cursor保持原有狀態。如果取值為FALSE，那麼表示將統計資訊物件相關的所有Cursor全部失效，目標SQL語句在下次執行時就會使用硬解析。如果設定為AUTO_INVALIDATE，那麼Oracle自己決定Shared Cursor失效動作，當SQL再次執行時間距離上次收集統計資訊的時間超過5小時（隱含引數“_OPTIMIZER_INVALIDATION_PERIOD”決定）則對SQL重新做硬解析。AUTO_INVALIDATE為預設選項。有些DBA在收集統計資訊時，沒有使用NO_INVALIDATE=>FALSE選項，所以，即使收集了統計資訊，執行計劃也不會立即改變。可以在表級別設定讓所有依賴於該表的遊標不失效，設定方法為：

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集SH.SALES表上的統計資訊時，讓所有依賴於該表的遊標不失效

---

---

實驗一：

下面實驗驗證了使用EXPLAIN PLAN FOR和SET AUTOT ON方式獲取到的執行計劃都是不準確的：

二、  如何在不執行SQL的情況下獲取執行計劃？

1、“EXPLAIN PLAN FOR SQL”不實際執行SQL語句，生成的計劃未必是真實執行的計劃。但是，必須要有PLAN_TABLE表，可以執行指令碼“@?/rdbms/admin/utlxplan.sql”來建立。

2、SQL*Plus的AUTOTRACE功能，命令：SET AUTOTRACE TRACEONLY EXPLAIN。除SET AUTOTRACE TRACEONLY EXPLAIN外其它的AUTOTRACE方式均實際執行SQL。但是，如果該命令後執行的是DML語句，那麼該DML語句是確實被Oracle實際執行過的。

三、  如何獲取SQL歷史執行計劃？

歷史執行計劃只能從AWR中獲取，如果AWR沒有記錄的話，那麼就無法獲取歷史執行計劃了，獲取歷史執行計劃的命令如下所示：

對於歷史計劃，可以生成SQL報告，命令如下所示：

其中，L_DBID代表資料庫的DBID，L_INST_NUM代表資料庫的例項號，單機環境為1，RAC環境填寫具體的例項號，L_BID為開始的快照號，L_EID為結束的快照號，L_SQLID為要檢視SQL的SQL_ID。

下面的例子可以直接從AWR中獲取SQL_ID為“bsa0wjtftg3uw”的執行計劃，可以看到歷史有2種執行計劃，一個是全表掃描，一個是索引範圍掃描：

四、  給出一個執行計劃的執行順序

閱讀如下的執行計劃，給出SQL的執行順序。

分析：採用最右最上最先執行的原則看層次關係，在同一級如果某個動作沒有子ID，那麼就最先執行，首先，6、7、9、13最右，所以，6，7最先執行做HASH JOIN，為6，7，5。

第二，8有子節點，接下來是9，8。

第三，HASH的結果和8的結果做FILTER過濾。

第四，10這個節點根據原則是11，13，12，10。

第五，剩下依次是3，2，1，0。

所以，該圖的執行順序是6，7，5，9，8，4，11，13，12，10，3，2，1，0。

---

---

為什麼預估執行計劃與真實執行計劃會有差異？

一問題概要

對同一個 SQL 語句的 ExplainPlan 裡顯示的預估執行計劃與通過 V$SQL_PLAN 檢視獲取的 Runtime Plan 真實執行計劃，偶爾會發現兩邊有不一致的情況，為什麼呢？為什麼預估執行計劃會不準確？怎樣才能避免這種情況的發生？

二問題解答

這是執行計劃相關中會被經常問道的問題，也是困擾自己很長時間的問題。希望通過下面的分析能解釋一部分原因。

對同一個 SQL 語句的 ExplainPlan 裡顯示的預估執行計劃與通過 V$SQL_PLAN 檢視獲取的真實執行計劃不一致的情況，其原因要比想象的更多種多樣。

 

• 繫結變數窺視（Bind Peeking）：Explain Plan 裡不會進行繫結變數窺視，但是 Runtime Plan 裡會進行繫結變數窺視，所以，如果這種情況發生會使兩個執行計劃會產生差異。

• 隱式轉換：Explain Plan 裡不會考慮繫結變數的型別，但是 Runtime Plan 裡會考慮型別，從而有可能會根據繫結變數的型別出現隱式轉換，所以謂詞（Predicate）會發生變化，使得執行計劃也會產生差異。

• 優化器引數：執行 Explain Plan 的 Session 與 Runtime Plan 的 Session 不是同一個。如果各個 Session 之間存在優化器引數差異，執行計劃也會產生差異。

• 統計資訊收集引數：Explain Plan 始終是用最新的統計資訊產生執行計劃，但是，Runtime Plan 不一定會用最新的統計資訊。因此也會產生執行計劃差異。

 

預估執行計劃與實際執行計劃產生差異的原因總結為上面幾種情況，當然也有因 Oracle Bug 的原因也會有產生執行計劃的差異情況。

下面通過幾個測試，加深對上面的問題的理解。

三測試環境

Oracle 版本是 11.2.0.1的情況。

 

SQL> SELECT * FROMV$VERSION WHERE ROWNUM <= 1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 –Production

生成表 T1，T1 表有如下特點：

表名	列名	列型別	說明
T1	C1	Number	“1”值有10,000個，“1~10000”的值各一個，總共有10,000種值
	C2	Varchar2	同上

之後，對列 C1、C2 分別生成單列索引 IDX_T1_C1 和IDX_T1_C2。

 

SQL> CREATE TABLET1 ( C1 INT , C2 VARCHAR2(10));

 

表已建立。

 

SQL> INSERT INTOT1 SELECT 1, '1' FROM DUAL CONNECT BY LEVEL <= 10000;

 

已建立10000行。

 

SQL> INSERT INTOT1 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;

 

已建立10000行。

 

SQL> CREATE INDEXIDX_T1_C1 ON T1(C1);

 

索引已建立。

 

SQL> CREATE INDEXIDX_T1_C2 ON T1(C2);

 

索引已建立。

對錶T1進行統計資訊收集。METHOD_OPT 的引數設為 ALLCOLUMNS SIZE 5 ，即，直方圖的 BUCKETS 個數指定為5。但是列 C1 和 C2 有 10,000個不同的值，BUCKETS 個數為5的話，會生成等高直方圖（HEIGHT BALANCED）。

SQL> EXECDBMS_STATS.gather_table_stats(user,'T1', method_opt =>'FOR ALL COLUMNS SIZE5');

 

PL/SQL 過程已成功完成。

 

收集統計資訊以後如下：

--table stats

SELECT t1.TABLE_NAME,

       t1.num_rows,

       t1.SAMPLE_SIZE

FROM   dba_tables t1

WHERE  table_name = 'T1'

AND    t1.OWNER = user;

 

TABLE_NAME   NUM_ROWS SAMPLE_SIZE

---------- ---------------------

T1              20000       20000

 

--column stats

SELECT t2.TABLE_NAME,

       t2.COLUMN_NAME,

      t2.NUM_DISTINCT,

       t2.NUM_NULLS,

       t2.DENSITY,

       t2.LOW_VALUE,

       t2.HIGH_VALUE,

       t2.HISTOGRAM

FROM   dba_tab_columns t2

WHERE  t2.table_name = 'T1'

AND    t2.OWNER = user';

 

TABLE COLUMNUM_DISTINCT NUM_NULLS DENSITY LOW_V HIGH_VALUE HISTOGRAM

----- ----------------- --------- ------- ----- ---------- --------------------

T1    C1          10000         0  0.00005 C102 C302       HEIGHT BALANCED

T1    C2          10000         0  0.00005 31   39393939   HEIGHT BALANCED

 

--histogram stats

select t3.TABLE_NAME

       ,t3.COLUMN_NAME

       ,t3.ENDPOINT_NUMBER

       ,t3.ENDPOINT_VALUE

from  dba_tab_histograms t3

WHERE  t3.table_name = 'T1'

AND    t3.OWNER = user;

 

TABLE COLUM ENDPOINTENDPOINT_VALUE

----- ----- ----------------------

T1    C1          2              1

T1    C1          3           2000

T1    C1          4           6000

T1    C1          5          10000

T1    C2          2 2.544225460682

T1    C2          3 2.607349087913

T1    C2          4 2.814229665870

T1    C2          5 2.971215519298

四案列1：繫結變數窺視（Bind Peeking）

下面我們看下，因繫結變數窺視，而引起的預估執行計劃與實際執行計劃不一致的情況。首先，啟用繫結變數窺視功能，預設值就是TRUE。

 

SQL> alter sessionset "_optim_peek_user_binds" = true;

 

會話已更改。

 

首先，我們輸出預估執行計劃。從下面可以看到，執行計劃選擇的是索引範圍掃描（Index Range Scan）的方式。

 

SQL> var b1number;

SQL> exec :b1 :=1;

 

PL/SQL 過程已成功完成。

 

SQL> explain planfor

  2 select count(c2)

  3  fromt1

  4 where c1 = :b1;

 

已解釋。

 

SQL> select * fromtable(dbms_xplan.display(null,null,'typical'));

 

 

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |    1 |     6 |     2  (0)| 00:00:01 |

|   1 | SORT AGGREGATE              |           |    1 |     6 |            |          |

|   2 |  TABLE ACCESS BY INDEX ROWID| T1       |     2 |    12 |    2   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN          |IDX_T1_C1 |     2 |       |    1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   3 - access("C1"=TO_NUMBER(:B1))

 

繫結變數B1的實際值是“1”。T1表裡值為1的記錄數將近佔50%，這種情況與其選擇索引範圍掃，不如選擇全表掃（Table Full Scan）會有效率一些。但是 ExplainPlan命令不進行繫結變數的窺視，即，在建立預估執行計劃的過程中，會把繫結變數的值設為未知（Uknown）來處理，不會考慮實際的繫結變數的值到底是什麼。所以，ExplainPlan 不關心其值是不是“1”，而只考慮 Distinct Count 來建立執行計劃。

等高直方圖（HEIGHT BALANCED）存在的時候，預估行數會通過 DistinctCount 列進行計算。計算公式如下：

 

預估行數 = 全部行數 / Distinct Count = 20,000 /10,000  = 2 

   

但是，實際執行計劃與上面的結果完全不一樣，如下。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = :b1;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'typical'));

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |     |       |       |   12 (100)|          |

|   1 | SORT AGGREGATE    |      |    1 |     6 |            |          |

|*  2 |  TABLE ACCESS FULL| T1   |  8000 | 48000 |    12  (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - filter("C1"=:B1)

可以看到，使用了繫結變數窺視，即，優化器在建立執行計劃前讀取了繫結變數的實際的值（進行是窺視）。之後，參考繫結變數的值來建立執行計劃。這個例子，使用了值“1”來建立了執行計劃。所以，預估行數從 ExplainPlan 裡的2 變成了8000。其原因如下：

預估行數 =  值“1”的 buckets 數 * buckets 的高度

               = 2 *（20000 / 5 ） = 8000

實際行數為10,001，預估值與實際值相當接近了。

使用 DBMS_XPLAN.DISPLAY_CURSOR函式的時候，引數裡如果加上 +PEEKED_BBINDS 的話，執行計劃裡可以看到繫結變數窺視的值。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = :b1;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'all +peeked_binds'));

 

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |     |       |       |   12 (100)|          |

|   1 | SORT AGGREGATE    |      |    1 |     6 |            |          |

|*  2 |  TABLE ACCESS FULL| T1   |  8000 | 48000 |    12  (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Query Block Name /Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   2 - SEL$1 / T1@SEL$1

 

Peeked Binds(identified by position):

--------------------------------------

 

   1 - :B1 (NUMBER): 1

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - filter("C1"=:B1)

 

Column ProjectionInformation (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT("C2")[22]

   2 - "C2"[VARCHAR2,10]

 

繫結變數窺視（Binding Peeking）與繫結變數捕獲（Bind Capture）經常弄混。繫結變數捕獲（Bind Capture）是對特定 SQL 裡使用的繫結變數值按照固定週期放到 SGA 裡儲存的情況。最初的繫結變數窺視與繫結變數捕獲的時間是一樣，約15分鐘（900秒）後，繫結變數捕獲會再次發生，週期性反覆發生。下面可以檢視繫結變數捕獲的資訊。

 

SELECT t4.NAME,

       t4.POSITION,

      t4.VALUE_STRING,

       t4.WAS_CAPTURED,

       t4.LAST_CAPTURED

FROM   V$sql_bind_capture t4

WHERE  sql_id = 'bqqp887001jj8';

 

NAME  POSITION VALUE WAS_C LAST_CAPTU

----- -------- ---------- ----------

:B1          1 1     YES  11-4月 -18

五案列2：繫結變數型別問題

首先，為了證明這個測試不是因為上面的繫結變數窺視而引起的不一致，所以把繫結變數窺視功能關掉了。

SQL> alter sessionset "_optim_peek_user_binds" = false;

 

會話已更改。

 

對 C2 列使用繫結變數，進行觀察。從下面可以看到，預估執行計劃裡使用了索引。

 

SQL> var b2number;

SQL> exec :b2 :=1;

 

PL/SQL 過程已成功完成。

 

SQL> explain planfor

  2  selectcount(c2)

  3  fromt1

  4 where c2 = :b2;

 

已解釋。

 

SQL> select * fromtable(dbms_xplan.display(null,null,'typical'));

 

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     1 |     4 |    1   (0)| 00:00:01 |

|   1 | SORT AGGREGATE   |           |    1 |     4 |            |         |

|*  2 |  INDEX RANGE SCAN| IDX_T1_C2 |    2 |     8 |     1  (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - access("C2"=:B2)

 

ExplainPlan 命令只會檢視是否存在繫結變數，而不會考慮繫結變數的型別是什麼，其值是什麼，始終會把繫結變數的型別設為 VARCHAR2 型別進行考慮。所以，上面的例子裡不管對繫結變數B2如何定義，ExplainPlan 裡預估執行計劃始終是一樣。

但是，真實執行計劃裡沒有選擇 INDEX RANGE SCAN，而是選擇了 TABLE FULL SCAN。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c2 = :b2;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 

-------------------------------------------------------------------------------------

| Id  | Operation          | Name | Starts | E-Rows | A-Rows|   A-Time   | Buffers |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |     |      1 |        |     1 |00:00:00.01 |      39 |

|   1 | SORT AGGREGATE    |      |     1 |      1 |      1 |00:00:00.01 |      39 |

|*  2 |  TABLE ACCESS FULL| T1   |      1 |     2 |  10001 |00:00:00.01 |      39 |

-------------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - filter(TO_NUMBER("C2")=:B2)

 

為什麼會發生這種情況？繫結變數窺視功能已經關閉了，所以肯定不是繫結變數窺視的問題。這裡需要注意的是，C2 列是 VARCHAR2 型別，繫結變數 B2 是 NUMBER型別。這時，Oracle 會進行隱式轉換，VARCHAR2 型別會被轉換成 NUMBER 型別，即，NUMBER 型別的優先順序更高。所以，會對C2列進行隱式轉換（VARCHAR2 →NUMBER），從而不能使用C2列的索引。可以在謂詞資訊（Predicate Information）中確認。

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - filter(TO_NUMBER("C2")=:B2)

 

為了再次證明這個是因為隱式轉換的問題，我們使用 VARCHAR2 型別的繫結變數 B3 進行測試。

 

SQL> var b3varchar2(10);

SQL> exec :b3 :='1';

 

PL/SQL 過程已成功完成。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c2 = :b3;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 

-----------------------------------------------------------------------------------------

| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |      1 |        |     1 |00:00:00.01 |      20 |

|   1 | SORT AGGREGATE   |           |      1 |     1 |      1 |00:00:00.01 |      20 |

|*  2 |  INDEX RANGE SCAN| IDX_T1_C2 |     1 |      2 |  10001 |00:00:00.01 |      20 |

-----------------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - access("C2"=:B3)

 

從上面可以看到，繫結變數型別是 VARCHAR2 的時候，沒有進行隱式轉換，產生了與預估執行計劃相同的執行計劃，使用了索引的範圍掃描。

這個例子也說明，不能完全相信預估的執行計劃。內部的一些轉換（比如列的隱式轉換）會使執行計劃改變，甚至有時候會出現不希望的執行計劃。

六案列3：統計資訊收集的引數問題

下面 SQL 的預估執行計劃與實際執行計劃完全一致。

 

 SQL> explain planfor

  2 select count(c2)

  3  fromt1

  4 where c1 = 2;

 

已解釋。

 

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |    1 |     6 |     2  (0)| 00:00:01 |

|   1 | SORT AGGREGATE              |           |    1 |     6 |            |          |

|   2 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |     6 |    2   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN          |IDX_T1_C1 |     1 |       |    1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   3 - access("C1"=2)

 

 

SQL> select  /*+ gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = 2;

 

 COUNT(C2)

----------

         1

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |      1 |       |      1 |00:00:00.01 |       3 |

|   1 | SORT AGGREGATE              |           |      1 |     1 |      1 |00:00:00.01 |       3 |

|   2 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |     1 |00:00:00.01 |       3 |

|*  3 |   INDEX RANGE SCAN          |IDX_T1_C1 |      1 |      1 |     1 |00:00:00.01 |       2 |

----------------------------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   3 - access("C1"=2)

 

對 T1 表的 C1 = 2，C2=1 的值增加20,000個，之後重新收集統計資訊，但是 NO_INVALIDATE 引數設為 NULL，NULL 的意思是讓 Oracle 自動處理的意思。NO_INVALIDATE 其他引數情況參考如下：

 

• NO_INVALIDATE=TRUE：更新統計資訊，但對有從屬（Dependency）關係的 SQL 不進行Invalidation。為了避免一次性大量的硬解析（Hard Parse）現象的發生。SQL 如果在 SGA 裡 Age Out 後，再次執行的時候，才會用到更新後的統計資訊。

• NO_INVALIDATE=FALSE：更新統計資訊，並對有從屬（Dependency）關係的 SQL 馬上進行 Invalidation。

• NO_INVALIDATE=AUTO(NULL)：更新統計資訊，但對有從屬關係的 SQL 不會一次性的進行 Invalidation，而是在最大5小時（18,000秒）內隨機進行 Invalidation 的方式進行。可以說是 TRUE 與 FALSE 的中間形式。18,000秒是可以通過 _OPTIMIZER_INVALIDATION_PERIOD 引數進行設定。

 

現在對錶T1增加資料，並收集統計資訊，但是 NO_INVALIDATE 引數設為 NULL（預設值是 NULL）。

 

 SQL> insert intot1 select 2,'1' from dual connect by level <= 20000;

 

已建立20000行。

 

SQL> execdbms_stats.gather_table_stats(user,'T1',method_opt => 'for all columns size5',no_invalidate => null);

 

PL/SQL 過程已成功完成。

 

Explain Plan 命令始終是在用最新的統計資訊，所以從下面可以看到，ExplainPlan 命令對 C1=2 的條件使用了最新的統計資訊，執行計劃選擇了 Table Full Scan。預估行數為 16,000 行，與實際行數 20,001 行數相當接近。因為存在列的直方圖，這種預估是可行的。

 

 SQL> explain planfor

  2 select count(t1.c2)

  3  fromsys.t1

  4 where t1.c1 = 2 ;

 

已解釋。

 

SQL> select * fromtable(dbms_xplan.display());

 

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |     |     1 |     6 |   20   (0)| 00:00:01 |

|   1 | SORT AGGREGATE    |      |    1 |     6 |            |          |

|*  2 |  TABLE ACCESS FULL| T1   | 16000 |96000 |    20   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   2 - filter("T1"."C1"=2)

 

但是，在真實執行計劃中仍然選擇了 Index Range Scan，因為雖然統計資訊更新了，但是相關的 SQL 還沒有被 Invalidation。

 

 SQL> select  /*+ gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = 2;

 

 COUNT(C2)

----------

     20001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 

----------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |  A-Time   | Buffers |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |           |     1 |        |      1 |00:00:00.02 |     102 |

|   1 | SORT AGGREGATE                |          |      1 |      1 |     1 |00:00:00.02 |     102 |

|   2 |  TABLE ACCESS BY INDEX ROWID | T1       |      1 |      2 | 20001 |00:00:00.02 |     102 |

|*  3 |   INDEX RANGE SCAN            | IDX_T1_C1 |      1 |     2 |  20001 |00:00:00.01 |      70 |

----------------------------------------------------------------------------------------------------

 

Predicate Information(identified by operation id):

---------------------------------------------------

 

   3 - access("C1"=2)

 

如果只看 Explain Plan 後就判斷“執行計劃的效率不錯”是不可取的，會根據不同的情況產生很大的效能差異。這時可以通過 DBMS_SHARED_POOL.PURGE 儲存過程，或使用 清理共享池（Shared Pool Flush）等方法強制反應最新的統計資訊。

七總結

預估執行計劃與真實執行計劃產生差異的原因，其實是多種多樣的，在分析其原因的過程中發現需要相當多的知識點。

產生差異的原因，其中最普遍的有因繫結變數的窺視，也有因繫結變數的隱式轉換，也有因引數差異，也有因統計資訊收集引數等問題。

不能對預估執行計劃100%信任，一定要實際執行以後驗證其結果。如果這個過程中想解釋執行計劃異常的現象，需要了解 DBMS_XPLAN 包的使用方法與對其結果的正確理解。