天天看點

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 包的使用方法與對其結果的正确了解。