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

SQL為什麼預估執行計劃與真實執行計劃會有差異？ http://www.bieryun.com/3149.html

一

問題概要

對同一個 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;

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;

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 |

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 * fromtable(dbms_xplan.display_cursor(null,null,'all +peeked_binds'));

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

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

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;

2  selectcount(c2)

4 where c2 = :b2;

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

| 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 |

2 - access("C2"=:B2)

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

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

3 where c2 = :b2;

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 |

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

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

為了再次證明這個是因為隐式轉換的問題，我們使用 VARCHAR2 類型的綁定變量 B3 進行測試。

SQL> var b3varchar2(10);

SQL> exec :b3 :='1';

3 where c2 = :b3;

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

| 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 |

2 - access("C2"=:B3)

從上面可以看到，綁定變量類型是 VARCHAR2 的時候，沒有進行隐式轉換，産生了與預估執行計劃相同的執行計劃，使用了索引的範圍掃描。

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

六

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

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

4 where c1 = 2;

|   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 |

3 - access("C1"=2)

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

3 where c1 = 2;

1

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

| 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 |

對 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);

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

2 select count(t1.c2)

3  fromsys.t1

4 where t1.c1 = 2 ;

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

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

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

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

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

20001

| 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 |

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

七

總結

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

産生差異的原因，其中最普遍的有因綁定變量的窺視，也有因綁定變量的隐式轉換，也有因參數差異，也有因統計資訊收集參數等問題。

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