【DB筆試面試586】在Oracle中，什麼是自適應游標共享（1）？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，什麼是自適應游標共享？

♣

答案部分

綁定變數窺探的副作用就在於，使用了綁定變數的目標SQL只會沿用之前硬解析時所產生的解析樹和執行計劃，即使這種沿用完全不適合於當前的情形。在Oracle 10g及其後續的版本中，Oracle會自動收集直方圖統計資訊，這意味著與之前的版本相比，在Oracle 10g及其後續的版本中Oracle有更大的概率會知道目標列實際數據的分布情況，也就是說綁定變數窺探的副作用將會更加明顯。當Oracle執行綁定變數窺探操作時綁定變數所對應的輸入值是否具有代表性就至關重要了（這裡「代表性」是指該輸入值所對應的執行計劃和該SQL在大多數情況下的執行計劃相同），因為這會直接決定此目標SQL在硬解析時所選擇的執行計劃，進而決定後續以軟解析/軟軟解析重複執行時所沿用的執行計劃。

為了解決上述綁定變數窺探所帶來的問題，Oracle在l1g中引入了自適應游標共享（Adaptive Cursor Sharing，ACS）。自適應游標共享可以讓使用了綁定變數的目標SQL在啟用了綁定變數窺探的前提條件下，不再像之前那樣必須刻板地只沿用之前硬解析時所產生的解析樹和執行計劃，而是讓目標SQL在其可能的多個執行計劃之間「自適應」地做出選擇。自適應游標共享的核心就在其能「自適應」地選擇執行計劃，從而就在一定程度上避免了綁定變數窺探的副作用。Oracle只需要在它認為目標SQL的執行計劃可能發生變化時，觸發該SQL再做一次硬解析就好了。因為一旦觸發了硬解析這個動作，Oracle就會將目標SQL重新再解析一遍，其中就包括對該SQL再做一次綁定變數窺探。顯然，再做一次綁定變數窺探後所對應的執行計劃就是當前情形下CBO認為的最優執行計劃，這個執行計劃很可能和該SQL硬解析時所產生的執行計劃不一樣了。也就是說，一個簡單的適時觸發目標SQL再做一次硬解析的動作就在一定程度上緩解了綁定變數窺探所帶來的副作用。

那麼Oracle會在什麼時候觸發上述硬解析動作？或者說這裡的「適時觸發」的具體含義是什麼？總的來說，Oracle會根據執行目標SQL時所對應的runtime統計資訊（比如所耗費的邏輯讀和CPU時間，對應結果集的行數等）的變化，以及當前傳入的綁定變數輸入值所在的謂詞條件的可選擇率，來綜合判斷是否需要觸發目標SQL的硬解析動作。

先介紹Oracle資料庫中與自適應游標共享相關的一些基本概念。

自適應游標共享要做的第一件事就是所謂的擴展游標共享（Extended Cursor Sharing），而擴展游標共享做的主要事情就是將目標SQL所對應的Child Cursor標記為Bind Sensitive。Bind Sensitive是指Oracle覺得某個含綁定變數的目標SQL的執行計劃可能會隨著所傳入的綁定變數輸入值的變化而變化。

當滿足如下三個條件時，目標SQL所對應的Child Cursor就會被Oracle標記為Bind Sensitive：

l 啟用了綁定變數窺探。

l 該SQL使用了綁定變數（不管是該SQL自帶的綁定變數，還是開啟常規游標共享後系統產生的綁定變數）。

l 該SQL使用的是不安全的謂詞條件（例如範圍查詢，目標列上有直方圖統計資訊的等值查詢等）。

自適應游標共享要做的第二件事情就是將目標SQL所對應的Child Cursor標記為Bind Aware。Bind Aware是指Oracle已經確定某個含綁定變數的目標SQL的執行計劃會隨著所傳入的綁定變數輸入值的變化而變化。

當滿足如下兩個條件時，目標SQL所對應的Child Cursor就會被Oracle標記為Bind Aware：

l 該SQL所對應的Child Cursor在之前已經被標記為Bind Sensitive。

l 該SQL在接下來連續兩次執行時，所對應的runtime統計資訊與該SQL之前硬解析時所對應的runtime統計資訊均存在較大差異。

對於自適應游標共享而言，V$SQL中的列IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE分別用來表示Child Cursor是否是Bind Sensitive、Bind Aware和共享的。這裡「共享」的含義是指存儲在該Child Cursor中的解析樹和執行計劃是否能被重用，一個非共享的Child Cursor中存儲的執行計劃和解析樹是不能被重用的，並且該Child Cursor也會在第一時間從Shared Pool中被清理出去。

與自適應游標共享相關的有兩個重要視圖分別是V$SQL_CS_STATISTICS和V$SQL_CS_SELECTIVITY：

l V$SQL_CS_STATISTICS用於顯示指定Child Cursor中存儲的runtime統計資訊。

l V$SQL_CS_SELECTIVITY用於顯示指定的、己經被標記為Bind Aware的Child Cursor中存儲的含綁定變數的謂詞條件所對應的可選擇率的範圍。當一個被標記為Bind Aware的Child Cursor所對應的目標SQL再次被執行時，Oracle就會比較當前傳入的綁定變數值所在的謂詞條件的可選擇率，以及該SQL之前硬解析時同名謂詞條件在V$SQL_CS_SELECTIVITY中對應的可選擇率的範圍，並以此來決定此時的執行是用硬解析還是軟解析／軟軟解析。

在介紹完上述基本概念後，現在就可以介紹自適應游標共享的整體執行流程了。Oracle資料庫中自適應游標共享的整體執行流程為如下所示：

（1）當目標SQL第一次被執行時，Oracle會用硬解析，同時Oracle會根據一系列條件（如該SQL有沒有使用綁定變數，參數CURSOR_SHARING的值是多少，綁定變數所在的列是否有直方圖，該SQL的WHERE條件是等值查詢還是範圍查詢等）來判斷是否將該SQL所對應的Child Cursor標記為Bind Sensitive。對於標記為Bind Sensitive的Child Cursor，Oracle會把執行該SQL時所對應的runtime統計資訊額外地存儲在該SQL所對應的Child Cursor中。

（2）當目標SQL第二次被執行時，Oracle會用軟解析，並且會重用該SQL第一次執行時所產生的Child Cursor中存儲的解析樹和執行計劃。

（3）當目標SQL第三次被執行時，如果該SQL所對應的Child Cursor己經被標記成了Bind Sensitive，同時Oracle在第二次和第三次執行該SQL時所記錄的runtime統計資訊和該SQL第一次硬解析時所記錄的runtime統計資訊均存在較大差異，那麼該SQL在第三次被執行時就會使用硬解析，Oracle此時會產生一個新的Child Cursor（這個新的Child Cursor會掛在原Parent Cursor下），並且Oracle會把這個新的Child Cursor標記為Bind Aware。

（4）對於標記為Bind Aware的Child Cursor所對應的目標SQL，當該SQL再次被執行時，Oracle就會根據當前傳入的綁定變數值所對應的謂詞條件的可選擇率，來決定此時是用硬解析還是用軟解析/軟軟解析。這裡的判斷原則是，如果當前傳入的綁定變數值所在的謂詞條件的可選擇率處於該SQL之前硬解析時同名謂詞條件在V$SQL_CS_STATISTICS中記錄的可選擇率的範圍之內，那麼此時Oracle就會使用軟解析／軟軟解析，並重用相關Child Cursor中存儲的解析樹和執行計劃，反之則是硬解析。如果是硬解析，且該次硬解析所產生的執行計劃和原有Child Cursor中存儲的執行計劃相同，那麼Oracle此時除了會新生成一個Child Cursor之外，還會把存儲相同執行計劃的原有Child Cursor標記為非共享（原有Child Cursor在V$SQL中對應記錄的列IS_SHAREABLE的值也會從Y變為N)，在把原有Child cursor標記為非共享的同時，Oracle還會對新生成的Child Cursor執行一個Cursor合併的過程（這裡Cursor合併的含義是指Oracle會合併存儲相同執行計劃的原有Child Cursor和新生成的Child Cursor）；如果是軟解析/軟軟解析，那麼Oracle會重用相關Child Cursor中存儲的解析樹和執行計劃。

自適應游標共享雖然在一定程度上緩解了綁定變數窺探所帶來的副作用，但它存在如下缺陷：

l 會額外地增加硬解析數量。

l 會額外地增加同一個父游標下子游標的數量，這會增加軟解析/軟軟解析時查找匹配子游標的工作量。

l 為了存儲這些額外增加地子游標，共享池（Shared Pool）在空間方面也會承受額外的壓力。所以當從Oracle 10g升級到11g時，建議適當增加共享池的大小。

l 若綁定變數個數超過14個，則ACS失效。

如果因為開啟ACS而導致系統產生了過多的子游標，進而導致共享池的空間緊張或者過多的Mutex等待，那麼可以通過如下任意一種方式來禁用ACS：

l 將隱含參數「_OPTIMIZER_EXTENDED_CURSOR_SHARING」和「_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL」的值均設為NONE，這樣就相當於關閉了可擴展游標共享。一旦可擴展游標共享被禁，所有的Child Cursor都將不能再被標記為Bind Sensitive，那麼自然就不能被標記為Bind Aware，也就是說此時自適應游標共享就相當於被禁掉了。

l 將隱含參數「_OPTIMIZER_ADAPTIVE_CURSOR_SHARING」的值設為FALSE。一旦此隱含參數的值被設為FALSE，則所有的Child Cursor都將不能再被標記為Bind Aware（即使它們己經被標記成了Bind Sensitive），也就是說此時自適應游標共享就被直接禁掉了。

這裡需要注意的是，自適應游標共享在Oracle 11g中有一個硬限制——只有當目標SQL中的綁定變數（不管這個綁定變數是該SQL自帶的還是開啟常規游標共享後系統產生的）的個數不超過14個時，自適應游標共享才會生效；一旦超過14，則該SQL對應的Child Cursor就永遠不會被標記為Bind Sensitive，那麼自適應游標共享就失效了。

下面給出一個自適應游標的示例：

資料庫版本為11.2.0.3，準備基礎數據：

CREATE TABLE T_ACS_20170611_LHR AS SELECT * FROM DBA_OBJECTS;  CREATE INDEX IDX_ACS_OBJID_LHR ON  T_ACS_20170611_LHR(OBJECT_ID);  SELECT COUNT(1) FROM T_ACS_20170611_LHR;  UPDATE T_ACS_20170611_LHR T SET T.OBJECT_TYPE='TABLE' WHERE ROWNUM<=60001;   --更新數據，讓OBJECT_TYPE變得不均衡  UPDATE T_ACS_20170611_LHR T SET T.OBJECT_TYPE='CLUSTER' WHERE ROWNUM<=2;  COMMIT;  LHR@orclasm > SELECT T.OBJECT_TYPE,COUNT(*) COUNTS FROM T_ACS_20170611_LHR T GROUP BY T.OBJECT_TYPE ORDER BY 2 DESC;    OBJECT_TYPE             COUNTS  ------------------- ----------  TABLE                    61818  SYNONYM                   3718  INDEX                     3082  JAVA CLASS                2381  VIEW                      1231  TYPE                       973  INDEX PARTITION            738  TRIGGER                    592  INDEX SUBPARTITION         585  PACKAGE                    560  PACKAGE BODY               545  LOB                        541  TABLE PARTITION            315  TABLE SUBPARTITION         223  FUNCTION                   159  LOB SUBPARTITION           150  LOB PARTITION              121  SEQUENCE                   109  TYPE BODY                   96  PROCEDURE                   55  JAVA RESOURCE               31  OPERATOR                    25  LIBRARY                     20  QUEUE                       19  RULE SET                    16  DIRECTORY                   14  DATABASE LINK               12  XML SCHEMA                   7  DIMENSION                    5  PROGRAM                      5  EVALUATION CONTEXT           5  JAVA DATA                    4  MATERIALIZED VIEW            4  RULE                         4  JOB                          2  CLUSTER                      2  JAVA SOURCE                  2  CONTEXT                      2  INDEXTYPE                    2  UNDEFINED                    1    --執行WHERE條件中帶OBJECT_TYPE列的SQL語句，以便讓基表COL_USAGE$可以記錄下該列，便於後續自動收集該列上的統計資訊：  LHR@orclasm > SELECT OO.NAME             OWNER,    2         O.NAME              TABLE_NAME,    3         C.NAME              COLUMN_NAME,    4         U.EQUALITY_PREDS,    5         U.EQUIJOIN_PREDS,    6         U.NONEQUIJOIN_PREDS,    7         U.RANGE_PREDS,    8         U.LIKE_PREDS,    9         U.NULL_PREDS,   10         U.TIMESTAMP   11    FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C   12   WHERE O.OBJ# = U.OBJ#   13     AND OO.USER# = O.OWNER#   14     AND C.OBJ# = U.OBJ#   15     AND C.COL# = U.INTCOL#   16   AND O.NAME='T_ACS_20170611_LHR'   17  ;    no rows selected    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE='TABLE';      COUNT(*)  ----------       61818    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE='CLUSTER';      COUNT(*)  ----------           2    LHR@orclasm > SELECT OO.NAME             OWNER,    2         O.NAME              TABLE_NAME,    3         C.NAME              COLUMN_NAME,    4         U.EQUALITY_PREDS,    5         U.EQUIJOIN_PREDS,    6         U.NONEQUIJOIN_PREDS,    7         U.RANGE_PREDS,    8         U.LIKE_PREDS,    9         U.NULL_PREDS,   10         U.TIMESTAMP   11    FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C   12   WHERE O.OBJ# = U.OBJ#   13     AND OO.USER# = O.OWNER#   14     AND C.OBJ# = U.OBJ#   15     AND C.COL# = U.INTCOL#   16   AND O.NAME='T_ACS_20170611_LHR'   17  ;    no rows selected    LHR@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;    PL/SQL procedure successfully completed.    LHR@orclasm > SELECT OO.NAME             OWNER,    2         O.NAME              TABLE_NAME,    3         C.NAME              COLUMN_NAME,    4         U.EQUALITY_PREDS,    5         U.EQUIJOIN_PREDS,    6         U.NONEQUIJOIN_PREDS,    7         U.RANGE_PREDS,    8         U.LIKE_PREDS,    9         U.NULL_PREDS,   10         U.TIMESTAMP   11    FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C   12   WHERE O.OBJ# = U.OBJ#   13     AND OO.USER# = O.OWNER#   14     AND C.OBJ# = U.OBJ#   15     AND C.COL# = U.INTCOL#   16   AND O.NAME='T_ACS_20170611_LHR'   17  ;    OWNER                          TABLE_NAME                     COLUMN_NAME                    EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP  ------------------------------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ---------- -------------------  LHR                            T_ACS_20170611_LHR             OBJECT_TYPE                                 1              0                 0           0          0          0 2017-06-11 08:34:34    LHR@orclasm >  LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ACS_20170611_LHR',ESTIMATE_PERCENT => 100,CASCADE => TRUE,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',NO_INVALIDATE => FALSE);    PL/SQL procedure successfully completed.    LHR@orclasm >  LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_BUCKETS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME='T_ACS_20170611_LHR' AND D.COLUMN_NAME='OBJECT_TYPE';    COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM  ------------------------------ ------------ ----------- ---------------  OBJECT_TYPE                              40          40 FREQUENCY    --在保持隱含參數「_OPTIM_PEEK_USER_BINDS」和參數CURSOR_SHARING的值均為其默認值的條件下，定義綁定變數接著實驗：  LHR@orclasm > ALTER SYSTEM FLUSH SHARED_POOL; --生產庫慎用    System altered.    LHR@orclasm > conn lhr/lhr  Connected.  LHR@orclasm > VAR X VARCHAR2(30);  LHR@orclasm > EXEC :X :='CLUSTER';    PL/SQL procedure successfully completed.    LHR@orclasm >  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;      COUNT(*)  ----------           2    LHR@orclasm > col SQL_TEXT format a88  LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';    SQL_TEXT                                                                                 SQL_ID        VERSION_COUNT  ---------------------------------------------------------------------------------------- ------------- -------------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X                         bt8tk3f1tnwcf             1      LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I  ------------- ------------ ---------- ----------- - - -  bt8tk3f1tnwcf            0          1          54 Y N Y  

目標SQL的謂詞條件為「OBJECT_TYPE=:X」，這個謂詞條件是一個含綁定變數的等值查詢條件，而且目標列OBJECT_TYPE上有FREQUENCY類型的直方圖統計資訊，所以該謂詞條件是一個不安全的謂詞條件。同時此SQL在執行時又啟用了綁定變數窺探，這意味著Oracle會把該SQL對應的Child Cursor標記為Bind Sensitive。

從上述查詢結果可以看到，目標SQL對應的IS_BIND_SENSITIVE的值為Y，IS_BIND_AWARE的值為N，IS_SHAREABLE的值為Y，這說明該SQL對應的Child Cursor確實己經被Oracle標記為Bind Sensitive；同時，該Child Cursor也是可共享的，但它現在還不是Bind Aware的。另外，上述Child Cursor所對應的runtime統計資訊BUFFER_GETS（即邏輯讀）的值為54，這是正常的，因為當綁定變數的值為「CLUSTER」時，目標SQL所對應結果集的Cardinality的值僅為2。

LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',0,'advanced'));    PLAN_TABLE_OUTPUT  --------------------------------------------------------------  SQL_ID  bt8tk3f1tnwcf, child number 0  -------------------------------------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X    Plan hash value: 3002671579    ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   |       |       |     3 (100)|          |  |   1 |  SORT AGGREGATE   |                   |     1 |     7 |            |          |  |*  2 |   INDEX RANGE SCAN| IDX_ACS_OBJID_LHR |     2 |    14 |     3   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------    Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------       1 - SEL$1     2 - SEL$1 / T@SEL$1    Outline Data  -------------      /*+        BEGIN_OUTLINE_DATA        IGNORE_OPTIM_EMBEDDED_HINTS        OPTIMIZER_FEATURES_ENABLE('11.2.0.3')        DB_VERSION('11.2.0.3')        ALL_ROWS        OUTLINE_LEAF(@"SEL$1")        INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))        END_OUTLINE_DATA    */    Peeked Binds (identified by position):  --------------------------------------       1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."OBJECT_TYPE"=:X)    Column Projection Information (identified by operation id):  -----------------------------------------------------------       1 - (#keys=0) COUNT(*)[22]      49 rows selected.    LHR@orclasm >  

從上述顯示內容可以看出，Oracle此時選擇的執行計劃是走對索引IDX_ACS_OBJID_LHR的索引範圍掃描。注意到「Peeked Binds」部分的內容為「1 – :X (VARCHAR2(30), CSID=852): 'CLUSTER'」，這說明Oracle在硬解析目標SQL的過程中確實使用了綁定變數窺探，且做「窺探」這個動作時看到的綁定變數攴的輸入值為「CLUSTER」。

現在將X的值修改為"TABLE」：

LHR@orclasm > EXEC :X :='TABLE';    PL/SQL procedure successfully completed.    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;      COUNT(*)  ----------       61818  LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';    SQL_TEXT                                                                                 SQL_ID        VERSION_COUNT EXECUTIONS  ---------------------------------------------------------------------------------------- ------------- ------------- ----------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X                         bt8tk3f1tnwcf             1          2    LHR@orclasm >    LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',0,'advanced'));    PLAN_TABLE_OUTPUT  ----------------------------------------------------  SQL_ID  bt8tk3f1tnwcf, child number 0  -------------------------------------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X    Plan hash value: 3002671579    ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   |       |       |     3 (100)|          |  |   1 |  SORT AGGREGATE   |                   |     1 |     7 |            |          |  |*  2 |   INDEX RANGE SCAN| IDX_ACS_OBJID_LHR |     2 |    14 |     3   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------    Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------       1 - SEL$1     2 - SEL$1 / T@SEL$1    Outline Data  -------------      /*+        BEGIN_OUTLINE_DATA        IGNORE_OPTIM_EMBEDDED_HINTS        OPTIMIZER_FEATURES_ENABLE('11.2.0.3')        DB_VERSION('11.2.0.3')        ALL_ROWS        OUTLINE_LEAF(@"SEL$1")        INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))        END_OUTLINE_DATA    */    Peeked Binds (identified by position):  --------------------------------------       1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."OBJECT_TYPE"=:X)    Column Projection Information (identified by operation id):  -----------------------------------------------------------       1 - (#keys=0) COUNT(*)[22]      49 rows selected.    LHR@orclasm >  LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I  ------------- ------------ ---------- ----------- - - -  bt8tk3f1tnwcf            0          2         309 Y N Y  

可以看到此時VERSION_COUNT的值為1，列EXECUTIONS的值為2，說明Oracle在第二次執行目標SQL時用的是軟解析；從目標SQL的執行計劃現在依然走的是對索引IDX_ACS_OBJID_LHR的索引範圍掃描，並且「Peeked Binds」部分的內容依然為「1 – :X (VARCHAR2(30), CSID=852): 'CLUSTER'」。顯然，這裡Oracle沿用了之前硬解析時對應的解析樹和執行計劃，即綁定變數窺探起作用了。

從查詢結果也可以看到，目標SQL對應的IS_BIND_SENSITIVE的值為Y，IS_BIND_AWARE的值為N，IS_SHAREABLE的值為Y，與之前比這些值均沒有發生變化。但我們注意到，上述Child Cursor所對應的runtime統計資訊BUFFER GETS的值從之前的54猛增到現在的309，己經有了較大變化，不過這也是正常的。因為當綁定變數攴的值為「TABLE」時，目標SQL所對應結果集的cardinality的值是61818，而不再是之前的2了。

之前在介紹Bind Aware時己經提過：目標SQL所對應的Child Cursor被標記為Bind Aware的必要條件，就是該SQL在接下來連續兩次執行時所對應的runtime統計資訊和該SQL硬解析時所對應的runtime統計資訊均存在較大差異。雖然這裡邏輯讀BUFFER GETS的值確實發生了較大變化，但上述SQL在的值為「TABLE」的情況下只執行了一次，所以還不滿足被標記為Bind Aware的前提條件，IS_BIND_AWARE的值當然就是N了。

V$SQL_CS_SELECTIVITY用於顯示指定的、己經被標記為Bind Aware的Child Cursor中存儲的含綁定變數的謂詞條件所對應的可選擇率的範圍。上述Child Cursor還沒有被標記為Bind Aware，所以現在以目標SQL對應的SQL_ID去查詢視圖V$SQL_CS_SELECTIVITY時是看不到對應的記錄的：

LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf';    no rows selected    --在綁定變數X的值為TABLE的情況下再次執行目標SQL：  LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;      COUNT(*)  ----------       61818    LHR@orclasm >  

現在該SQL對應的Child Cursor己經被標記為Bind Sensitive了，且該SQL接下來連續兩次執行時所對應的runtime統計資訊，以及該SQL之前硬解析時所對應的runtime統計資訊均存在較大差異，那麼此時Oracle在執行該SQL時就會使用硬解析，即Oracle此時會產生一個新的Child Cursor（這個新的Child Cursor會掛在原Parent Cursor下），並且Oracle會把這個新的Child Cursor標記為Bind Aware。

LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';    SQL_TEXT                                                                                 SQL_ID        VERSION_COUNT EXECUTIONS  ---------------------------------------------------------------------------------------- ------------- ------------- ----------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X                         bt8tk3f1tnwcf             2          3    LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I  ------------- ------------ ---------- ----------- - - -  bt8tk3f1tnwcf            0          2         309 Y N N  bt8tk3f1tnwcf            1          1         522 Y Y Y  

可以看到，上述SQL對應的列VERSION_COUNT的值從之前的1變為現在的2，列EXECUTIONS的值為3，說明Oracle在第三次執行該SQL時確實用的是硬解析。V$SQL多了一個CHILD NUMBER為1的新Child Cursor，且該Child Cursor對應的IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE的值均為Y，這說明該SQL在本次硬解析時新生成的Child cursor確實己經被Oracle標記為Bind Aware，同時，該Child Cursor也是可共享的。

目標SQL現在的執行計劃為如下所示：

LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',1,'advanced'));    PLAN_TABLE_OUTPUT  ------------------------------------------------------------------------  SQL_ID  bt8tk3f1tnwcf, child number 1  -------------------------------------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X    Plan hash value: 4256744017    -------------------------------------------------------------------------------------------  | Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT      |                   |       |       |    89 (100)|          |  |   1 |  SORT AGGREGATE       |                   |     1 |     7 |            |          |  |*  2 |   INDEX FAST FULL SCAN| IDX_ACS_OBJID_LHR | 61818 |   422K|    89   (2)| 00:00:02 |  -------------------------------------------------------------------------------------------    Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------       1 - SEL$1     2 - SEL$1 / T@SEL$1    Outline Data  -------------      /*+        BEGIN_OUTLINE_DATA        IGNORE_OPTIM_EMBEDDED_HINTS        OPTIMIZER_FEATURES_ENABLE('11.2.0.3')        DB_VERSION('11.2.0.3')        ALL_ROWS        OUTLINE_LEAF(@"SEL$1")        INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))        END_OUTLINE_DATA    */    Peeked Binds (identified by position):  --------------------------------------       1 - :X (VARCHAR2(30), CSID=852): 'TABLE'    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("T"."OBJECT_TYPE"=:X)    Column Projection Information (identified by operation id):  -----------------------------------------------------------       1 - (#keys=0) COUNT(*)[22]      49 rows selected.  

從上述顯示內容可以看出，Oracle此時選擇的執行計劃是走對索引IDX_ACS_OBJID_LHR的索引快速全掃描。注意到「Peeked Binds」部分的內容為「1 – :X (VARCHAR2(30), CSID=852): 'TABLE'」，說明Oracle在硬解析上述SQL的過程中確實再次使用了綁定變數窺探，且做「窺探」這個動作時看到的綁定變數的輸入值為"TABLE」。

CHILD_NUMBER為1的Child Cursor己經被標記成了Bind Aware，所以現在以目標SQL對應的SQL_ID去查詢視圖V$SQL_CS_SELECTIVITY時就能看到對應的記錄了：

LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf';    ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH  ---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------  00000000AA2108A8 2207936910 bt8tk3f1tnwcf            1 =X                                                0 0.711697   0.869852    LHR@orclasm > SELECT * FROM V$SQL_CS_STATISTICS D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY D.CHILD_NUMBER;    ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME  ---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------  00000000AA2108A8 2207936910 bt8tk3f1tnwcf            0           821942781 Y          1              3          54          0  00000000AA2108A8 2207936910 bt8tk3f1tnwcf            1          3197905255 Y          1          61819         522          0  

從上述顯示內容可以看出，原SQL中的謂詞條件「=:x」對應的可選擇率的範圍為[0.711697，0.869852]，即可選擇率範圍的下限為0.711697，上限為0.869852。

這個可選擇率的範圍是如何算出來的呢？Oracle首先計算做硬解析時（做了綁定變數窺探後）上述謂詞條件的可選擇率（這裡將計算出來的可選擇率記為S)，然後將S上下浮動10％就得到了上述可選擇率的範圍，即可選擇率範圍的計算公式為[0.9*S，1.1*S]。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。