對同一個 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 下面我們看下，因綁定變量窺視，而引起的預估執行計劃與實際執行計劃不一緻的情況。首先，激活綁定變量窺視功能，預設值就是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 首先，為了證明這個測試不是因為上面的綁定變量窺視而引起的不一緻，是以把綁定變量窺視功能關掉了。
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 包的使用方法與對其結果的正确了解。
原文釋出時間為：2018-04-12
本文作者：郭成日
本文來自雲栖社群合作夥伴“
資料和雲”，了解相關資訊可以關注“
”。
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)