為什么預(yù)估執(zhí)行計(jì)劃與真實(shí)執(zhí)行計(jì)劃會(huì)有差異？
一、問題概要
對(duì)同一個(gè)SQL語句的ExplainPlan里顯示的預(yù)估執(zhí)行計(jì)劃與通過V$SQL_PLAN視圖獲取的Runtime Plan真實(shí)執(zhí)行計(jì)劃，偶爾會(huì)發(fā)現(xiàn)兩邊有不一致的情況奸鸯，為什么呢？為什么預(yù)估執(zhí)行計(jì)劃會(huì)不準(zhǔn)確稽荧？怎樣才能避免這種情況的發(fā)生年碘？
二澈歉、問題解答
這是執(zhí)行計(jì)劃相關(guān)中會(huì)被經(jīng)常問道的問題，也是困擾自己很長(zhǎng)時(shí)間的問題屿衅。希望通過下面的分析能解釋一部分原因埃难。
對(duì)同一個(gè)SQL語句的ExplainPlan里顯示的預(yù)估執(zhí)行計(jì)劃與通過V$SQL_PLAN視圖獲取的真實(shí)執(zhí)行計(jì)劃不一致的情況，其原因要比想象的更多種多樣傲诵。
[if !supportLists]l? [endif]綁定變量窺視（Bind Peeking）：Explain Plan里不會(huì)進(jìn)行綁定變量窺視凯砍，但是Runtime Plan里會(huì)進(jìn)行綁定變量窺視，所以拴竹，如果這種情況發(fā)生會(huì)使兩個(gè)執(zhí)行計(jì)劃會(huì)產(chǎn)生差異悟衩。
[if !supportLists]l? [endif]隱式轉(zhuǎn)換：Explain Plan里不會(huì)考慮綁定變量的類型，但是Runtime Plan里會(huì)考慮類型栓拜，從而有可能會(huì)根據(jù)綁定變量的類型出現(xiàn)隱式轉(zhuǎn)換座泳，所以謂詞（Predicate）會(huì)發(fā)生變化，使得執(zhí)行計(jì)劃也會(huì)產(chǎn)生差異幕与。
[if !supportLists]l? [endif]優(yōu)化器參數(shù)：執(zhí)行Explain Plan的Session與Runtime Plan的Session不是同一個(gè)挑势。如果各個(gè)Session之間存在優(yōu)化器參數(shù)差異，執(zhí)行計(jì)劃也會(huì)產(chǎn)生差異啦鸣。
[if !supportLists]l? [endif]統(tǒng)計(jì)信息收集參數(shù)：Explain Plan始終是用最新的統(tǒng)計(jì)信息產(chǎn)生執(zhí)行計(jì)劃潮饱，但是，Runtime Plan不一定會(huì)用最新的統(tǒng)計(jì)信息诫给。因此也會(huì)產(chǎn)生執(zhí)行計(jì)劃差異香拉。
?? ?預(yù)估執(zhí)行計(jì)劃與實(shí)際執(zhí)行計(jì)劃產(chǎn)生差異的原因總結(jié)為上面幾種情況，當(dāng)然也有因Oracle Bug的原因也會(huì)有產(chǎn)生執(zhí)行計(jì)劃的差異情況中狂。
下面通過幾個(gè)測(cè)試凫碌，加深對(duì)上面的問題的理解。
三胃榕、測(cè)試環(huán)境
Oracle 版本是 11.2.0.1的情況盛险。
SQL> SELECT * FROMV$VERSION WHERE ROWNUM <= 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 –Production
生成表 T1，T1表有如下特點(diǎn)：
表名列名列類型說明
T1C1Number“1”值有10,000個(gè)勋又，“1~10000”的值各一個(gè)苦掘，總共有10,000種值
C2Varchar2同上
之后，對(duì)列C1楔壤、C2分別生成單列索引 IDX_T1_C1 和IDX_T1_C2鹤啡。
SQL> CREATE TABLET1 ( C1 INT , C2 VARCHAR2(10));
表已創(chuàng)建。
SQL> INSERT INTOT1 SELECT 1, '1' FROM DUAL CONNECT BY LEVEL <= 10000;
已創(chuàng)建10000行挺邀。
SQL> INSERT INTOT1 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
已創(chuàng)建10000行揉忘。
SQL> CREATE INDEXIDX_T1_C1 ON T1(C1);
索引已創(chuàng)建。
SQL> CREATE INDEXIDX_T1_C2 ON T1(C2);
索引已創(chuàng)建端铛。
對(duì)表T1進(jìn)行統(tǒng)計(jì)信息收集泣矛。METHOD_OPT 的參數(shù)設(shè)為 ALL
COLUMNS SIZE 5 ，即禾蚕，直方圖的BUCKETS個(gè)數(shù)指定為5您朽。但是列C1和C2有 10,000個(gè)不同的值，BUCKETS個(gè)數(shù)為5的話换淆，會(huì)生成等高直方圖（HEIGHT BALANCED）哗总。
SQL> EXECDBMS_STATS.gather_table_stats(user,'T1', method_opt =>'FOR ALL COLUMNS SIZE5');
PL/SQL 過程已成功完成。
收集統(tǒng)計(jì)信息以后如下：
--table stats
Set linesize 100
SELECT t1.owner,
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
Set linesize 120
Col table_name formata15
Col column_nameformat a10
Col low_value formata15
Col high_value formata15
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
Set linesize 100
Col table_name formata20
Col column_nameformat a20
select t3.TABLE_NAME
?????? ,t3.COLUMN_NAME
?????? ,t3.ENDPOINT_NUMBER
?????? ,to_char(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）
下面我們看下讯屈，因綁定變量窺視，而引起的預(yù)估執(zhí)行計(jì)劃與實(shí)際執(zhí)行計(jì)劃不一致的情況县习。首先涮母，激活綁定變量窺視功能，默認(rèn)值就是TRUE躁愿。
SQL> alter sessionset "_optim_peek_user_binds" = true;
會(huì)話已更改叛本。
首先，我們輸出預(yù)估執(zhí)行計(jì)劃彤钟。從下面可以看到来候，執(zhí)行計(jì)劃選擇的是索引范圍掃描（Index Range Scan）的方式。
SQL> var b1 number;
SQL> exec :b1 :=1;
PL/SQL 過程已成功完成逸雹。
SQL> explain planfor
??? select count(c2)
??? from t1
??? 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的實(shí)際值是“1”。T1表里值為1的記錄數(shù)將近占50%峡眶，這種情況與其選擇索引范圍掃剧防，不如選擇全表掃（Table Full Scan）會(huì)有效率一些。但是 ExplainPlan命令不進(jìn)行綁定變量的窺視辫樱，即峭拘，在創(chuàng)建預(yù)估執(zhí)行計(jì)劃的過程中砂代，會(huì)把綁定變量的值設(shè)為未知（Uknown）來處理喘垂，不會(huì)考慮實(shí)際的綁定變量的值到底是什么。所以懈贺，ExplainPlan不關(guān)心其值是不是“1”搬男，而只考慮Distinct Count來建立執(zhí)行計(jì)劃拣展。
等高直方圖（HEIGHT BALANCED）存在的時(shí)候，預(yù)估行數(shù)會(huì)通過DistinctCount列進(jìn)行計(jì)算缔逛。計(jì)算公式如下：
預(yù)估行數(shù) = 全部行數(shù)/ Distinct Count = 20,000 /10,000? =?2?
但是备埃，實(shí)際執(zhí)行計(jì)劃與上面的結(jié)果完全不一樣姓惑，如下。
SQL> select /*+gather_plan_statistics */ count(c2)
??? from t1
??? 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)
可以看到按脚，使用了綁定變量窺視于毙，即，優(yōu)化器在創(chuàng)建執(zhí)行計(jì)劃前讀取了綁定變量的實(shí)際的值（進(jìn)行是窺視）辅搬。之后唯沮，參考綁定變量的值來創(chuàng)建執(zhí)行計(jì)劃。這個(gè)例子堪遂，使用了值“1”來創(chuàng)建了執(zhí)行計(jì)劃介蛉。所以，預(yù)估行數(shù)從ExplainPlan里的2 變成了8000溶褪。其原因如下：
?? 預(yù)估行數(shù)=?值“1”的buckets 數(shù) * buckets 的高度
????????????? =?2 *（20000 / 5 ）=?8000
實(shí)際行數(shù)為10,001,預(yù)估值與實(shí)際值相當(dāng)接近了币旧。
使用DBMS_XPLAN.DISPLAY_CURSOR函數(shù)的時(shí)候，參數(shù)里如果加上 +PEEKED_BBINDS的話猿妈，執(zhí)行計(jì)劃里可以看到綁定變量窺視的值佳恬。
SQL> select /*+gather_plan_statistics */ count(c2)
?? ??fromt1
??? ?wherec1 = :b1;
?COUNT(C2)
----------
???? 10001
SQL> select * from
table(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）經(jīng)常弄混。綁定變量捕獲（Bind Capture）是對(duì)特定SQL里使用的綁定變量值按照固定周期放到SGA里保存的情況于游。最初的綁定變量窺視與綁定變量捕獲的時(shí)間是一樣毁葱，約15分鐘（900秒）后，綁定變量捕獲會(huì)再次發(fā)生贰剥，周期性反復(fù)發(fā)生倾剿。下面可以查看綁定變量捕獲的信息。
select * from v$sql twhere t.sql_text like '%from t1%'
SELECT t4.NAME,
????? ?t4.POSITION,
?????? t4.VALUE_STRING,
?????? t4.WAS_CAPTURED,
?????? t4.LAST_CAPTURED
FROM?? V$sql_bind_capture t4
WHERE? sql_id = '0cb0dywxmcpwf';
NAME? POSITION VALUE WAS_C LAST_CAPTU
----- -------- ---------- ----------
:B1? 1??? 1??? YES? 2018/5/916:28:06
五蚌成、案列2：綁定變量類型問題
首先前痘，為了證明這個(gè)測(cè)試不是因?yàn)樯厦娴慕壎ㄗ兞扛Q視而引起的不一致，所以把綁定變量窺視功能關(guān)掉了担忧。
SQL> alter sessionset "_optim_peek_user_binds" = false;
會(huì)話已更改芹缔。
對(duì)C2列使用綁定變量，進(jìn)行觀察瓶盛。從下面可以看到最欠，預(yù)估執(zhí)行計(jì)劃里使用了索引。
SQL> var b2number;
SQL> exec :b2 :=1;
PL/SQL 過程已成功完成惩猫。
SQL> explain planfor
? ??select count(c2)
??? from t1
??? 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命令只會(huì)查看是否存在綁定變量，而不會(huì)考慮綁定變量的類型是什么轧房，其值是什么拌阴，始終會(huì)把綁定變量的類型設(shè)為VARCHAR2類型進(jìn)行考慮。所以奶镶，上面的例子里不管對(duì)綁定變量B2如何定義迟赃，ExplainPlan里預(yù)估執(zhí)行計(jì)劃始終是一樣陪拘。
但是，真實(shí)執(zhí)行計(jì)劃里沒有選擇INDEX RANGE SCAN纤壁，而是選擇了 TABLE FULL SCAN藻丢。
SQL> select /*+gather_plan_statistics */ count(c2)
?? from t1
??? 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)
為什么會(huì)發(fā)生這種情況？綁定變量窺視功能已經(jīng)關(guān)閉了摄乒，所以肯定不是綁定變量窺視的問題。這里需要注意的是残黑，C2列是VARCHAR2類型馍佑，綁定變量B2是NUMBER類型。這時(shí)梨水，Oracle會(huì)進(jìn)行隱式轉(zhuǎn)換拭荤，VARCHAR2類型會(huì)被轉(zhuǎn)換成NUMBER類型，即疫诽，NUMBER類型的優(yōu)先級(jí)更高舅世。所以，會(huì)對(duì)C2列進(jìn)行隱式轉(zhuǎn)換（VARCHAR2 →NUMBER）奇徒，從而不能使用C2列的索引雏亚。可以在謂詞信息（Predicate Information）中確認(rèn)摩钙。
Predicate Information(identified by operation id):
---------------------------------------------------
?? 2 - filter(TO_NUMBER("C2")=:B2)
為了再次證明這個(gè)是因?yàn)殡[式轉(zhuǎn)換的問題罢低，我們使用VARCHAR2類型的綁定變量B3進(jìn)行測(cè)試。
SQL> var b3varchar2(10);
SQL> exec :b3 :='1';
PL/SQL 過程已成功完成胖笛。
SQL> select /*+gather_plan_statistics */ count(c2)
??? from t1
??? 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的時(shí)候，沒有進(jìn)行隱式轉(zhuǎn)換长踊，產(chǎn)生了與預(yù)估執(zhí)行計(jì)劃相同的執(zhí)行計(jì)劃功舀，使用了索引的范圍掃描。
這個(gè)例子也說明身弊，不能完全相信預(yù)估的執(zhí)行計(jì)劃辟汰。內(nèi)部的一些轉(zhuǎn)換（比如列的隱式轉(zhuǎn)換）會(huì)使執(zhí)行計(jì)劃改變，甚至有時(shí)候會(huì)出現(xiàn)不希望的執(zhí)行計(jì)劃阱佛。
六莉擒、案列3：統(tǒng)計(jì)信息收集的參數(shù)問題
下面SQL的預(yù)估執(zhí)行計(jì)劃與實(shí)際執(zhí)行計(jì)劃完全一致。
SQL> explain planfor
??? select count(c2)
??? from t1
??? where c1 = 2;
已解釋瘫絮。
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???????|???? 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)
??? from t1
??? 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 |? SORTAGGREGATE????????????? |?????????? |????? 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)
對(duì)T1表的 C1 = 2涨冀，C2=1的值增加20,000個(gè)，之后重新收集統(tǒng)計(jì)信息麦萤，但是NO_INVALIDATE參數(shù)設(shè)為NULL鹿鳖，NULL的意思是讓Oracle自動(dòng)處理的意思扁眯。NO_INVALIDATE其他參數(shù)情況參考如下：
[if !supportLists]l? [endif]NO_INVALIDATE=TRUE：更新統(tǒng)計(jì)信息，但對(duì)有從屬（Dependency）關(guān)系的SQL 不進(jìn)行Invalidation翅帜。為了避免一次性大量的硬解析（Hard Parse）現(xiàn)象的發(fā)生姻檀。SQL如果在SGA里Age Out后，再次執(zhí)行的時(shí)候涝滴，才會(huì)用到更新后的統(tǒng)計(jì)信息绣版。
[if !supportLists]l? [endif]NO_INVALIDATE=FALSE：更新統(tǒng)計(jì)信息，并對(duì)有從屬（Dependency）關(guān)系的SQL 馬上進(jìn)行Invalidation歼疮。
[if !supportLists]l? [endif]NO_INVALIDATE=AUTO(NULL)：更新統(tǒng)計(jì)信息杂抽，但對(duì)有從屬關(guān)系的SQL不會(huì)一次性的進(jìn)行Invalidation，而是在最大5小時(shí)（18,000秒）內(nèi)隨機(jī)進(jìn)行Invalidation的方式進(jìn)行韩脏∷豸铮可以說是TRUE與FALSE的中間形式。18,000秒是可以通過_OPTIMIZER_INVALIDATION_PERIOD參數(shù)進(jìn)行設(shè)定赡矢。
現(xiàn)在對(duì)表T1增加數(shù)據(jù)杭朱，并收集統(tǒng)計(jì)信息，但是NO_INVALIDATE參數(shù)設(shè)為NULL（默認(rèn)值是NULL）吹散。
SQL> insert intot1 select 2,'1' from dual connect by level <= 20000;
已創(chuàng)建20000行弧械。
SQL> execdbms_stats.gather_table_stats(user,'T1',method_opt => 'for all columns size5',no_invalidate => null);
PL/SQL 過程已成功完成。
Explain Plan命令始終是在用最新的統(tǒng)計(jì)信息空民，所以從下面可以看到梦谜，ExplainPlan命令對(duì)C1=2的條件使用了最新的統(tǒng)計(jì)信息，執(zhí)行計(jì)劃選擇了Table Full Scan袭景。預(yù)估行數(shù)為16,000行唁桩，與實(shí)際行數(shù)20,001行數(shù)相當(dāng)接近。因?yàn)榇嬖诹械闹狈綀D耸棒，這種預(yù)估是可行的荒澡。
SQL> explain planfor
??? select count(t1.c2)
??? from sys.t1
??? 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)
但是与殃，在真實(shí)執(zhí)行計(jì)劃中仍然選擇了Index Range Scan单山，因?yàn)殡m然統(tǒng)計(jì)信息更新了，但是相關(guān)的SQL還沒有被Invalidation幅疼。
SQL> select? /*+ gather_plan_statistics */ count(c2)
??? from t1
??? 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后就判斷“執(zhí)行計(jì)劃的效率不錯(cuò)”是不可取的米奸，會(huì)根據(jù)不同的情況產(chǎn)生很大的性能差異。這時(shí)可以通過DBMS_SHARED_POOL.PURGE存儲(chǔ)過程爽篷，或使用清理共享池（Shared Pool Flush）等方法強(qiáng)制反應(yīng)最新的統(tǒng)計(jì)信息悴晰。
七、總結(jié)
預(yù)估執(zhí)行計(jì)劃與真實(shí)執(zhí)行計(jì)劃產(chǎn)生差異的原因，其實(shí)是多種多樣的铡溪，在分析其原因的過程中發(fā)現(xiàn)需要相當(dāng)多的知識(shí)點(diǎn)漂辐。
產(chǎn)生差異的原因，其中最普遍的有因綁定變量的窺視棕硫，也有因綁定變量的隱式轉(zhuǎn)換髓涯，也有因參數(shù)差異，也有因統(tǒng)計(jì)信息收集參數(shù)等問題哈扮。
不能對(duì)預(yù)估執(zhí)行計(jì)劃100%信任纬纪，一定要實(shí)際執(zhí)行以后驗(yàn)證其結(jié)果。如果這個(gè)過程中想解釋執(zhí)行計(jì)劃異常的現(xiàn)象滑肉，需要了解DBMS_XPLAN包的使用方法與對(duì)其結(jié)果的正確理解包各。
