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

• 2019 年 10 月 10 日
• 筆記

從上述計算結果可以看出，現在計算出的可選擇率範圍為[0.014172，0.017322]，在CHILD_NUMBER為5的原有Child Cursor對應的可選擇率範圍[0.000023，0.052317]之內，所以剛才Oracle在執行上述SQL時（即第8次執行目標SQL）用的就是軟解析/軟軟解析，並且此時重用的就是CHILD_NUMBER為5的Child Cursor中存儲的解析樹和執行計劃。

從上述對自適應游標共享的整個測試過程可以看出，自適應游標共享雖然在一定程度上緩解了綁定變量窺探所帶來的副作用，但自適應游標共享並不是完美的，它可能存在如下缺陷：

l 可能導致一定數量的額外的硬解析（比如上述目標SQL總共執行了8次，但有6次都是硬解析）。

l 可能導致一定數量的額外的Child Cursor掛在同一個Parent Cursor下（比如上述目標SQL總共執行了8次，但產生了6個Child Cursor），這會增加軟解析/軟軟解析時查找匹配Child Cursor的工作量。

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

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

l 將隱含參數「_OPTIMIZER_EXTENDED_CURSOR_SHARING」和「_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL」的值均設為NONE，這樣就相當於關閉了可擴展游標共享。一旦可擴展游標共享被禁，所有的Child Cursor都將不能再被標記為Bind Sensitive。而被標記為Bind Sensitive是Child Cursor被後續標記為Bind Aware的前提條件，一旦不能被標記為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，那麼自適應游標共享就失效了。

SYS@orclasm > SET PAGESIZE 9999  SYS@orclasm > SET LINE 9999  SYS@orclasm > COL NAME FORMAT A40  SYS@orclasm > COL KSPPDESC FORMAT A66  SYS@orclasm > COL KSPPSTVL FORMAT A20  SELECT A.INDX,         A.KSPPINM NAME,         A.KSPPDESC,         B.KSPPSTVL  FROM   X$KSPPI  A,         X$KSPPCV B  WHERE  A.INDX = B.INDX  AND UPPER(A.KSPPINM) IN ('_OPTIMIZER_EXTENDED_CURSOR_SHARING','_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL','_OPTIMIZER_ADAPTIVE_CURSOR_SHARING');          INDX NAME                                     KSPPDESC                                                           KSPPSTVL  ---------- ---------------------------------------- ------------------------------------------------------------------ --------------------        1914 _optimizer_extended_cursor_sharing       optimizer extended cursor sharing                                  UDO        1915 _optimizer_extended_cursor_sharing_rel   optimizer extended cursor sharing for relational operators         SIMPLE        1916 _optimizer_adaptive_cursor_sharing       optimizer adaptive cursor sharing                                  TRUE  

這裡還是以前面測試自適應游標共享時所用到的表T_ACS_20170611_LHRTI為例來說明。

把CURSOR_SHARING的值改為FORCE，然後在保持隱含參數「_OPTIM_PEEK_USER_BINDS」值為其默認值的情況下清空Shared pool（為了排除干擾）：

LHR@orclasm > ALTER SESSION SET CURSOR_SHARING='FORCE';    Session altered.    LHR@orclasm > alter system flush shared_pool;    System altered.    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T    2  WHERE T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    3  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    4  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    5  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    6  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    7  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    8  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER' ;      COUNT(*)  ----------           2    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T    2  WHERE T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    3  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    4  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    5  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    6  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    7  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    8  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE' ;      COUNT(*)  ----------       61818    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T    2  WHERE T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    3  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    4  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    5  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    6  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    7  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    8  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE' ;      COUNT(*)  ----------       61818  

依據之前自適應游標共享的測試結果，可以推斷出如果自適應游標共享對上述SQL生效的話，則上述SQL在被執行了三次的情況下應該會產生兩個Child Cursor。

從如下查詢結果可以看到，上述SQL在被Oracle用系統產生的綁定變量替換後對應記錄的列VERSION_COUNT的值為2，列EXECUTIONS的值為3，這說明上述SQL在被執行了三次的情況下確實產生了兩個Child Cursor，即當目標SQL中的綁定變量的數量不超過14時，自適應游標共享確實生效了：

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=:"SYS_B_00" AND T.OBJECT_T 65ggkpkp6n7mq             2          3  YPE=:"SYS_B_01" AND T.OBJECT_TYPE=:"SYS_B_02" AND T.OBJECT_TYPE=:"SYS_B_03" AND T.OBJECT  _TYPE=:"SYS_B_04" AND T.OBJECT_TYPE=:"SYS_B_05" AND T.OBJECT_TYPE=:"SYS_B_06" AND T.OBJE  CT_TYPE=:"SYS_B_07" AND T.OBJECT_TYPE=:"SYS_B_08" AND T.OBJECT_TYPE=:"SYS_B_09" AND T.OB  JECT_TYPE=:"SYS_B_10" AND T.OBJECT_TYPE=:"SYS_B_11" AND T.OBJECT_TYPE=:"SYS_B_12" AND T.  OBJECT_TYPE=:"SYS_B_13"    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,A.PLAN_HASH_VALUE FROM V$SQL A WHERE A.SQL_ID='65ggkpkp6n7mq';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE  ------------- ------------ ---------- ----------- - - - ---------------  65ggkpkp6n7mq            0          2         309 Y N N      2878087074  65ggkpkp6n7mq            1          1         522 Y Y Y      3865303624    LHR@orclasm >  

該目標SQL多執行幾次後：

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=:"SYS_B_00" AND T.OBJECT_T 65ggkpkp6n7mq             3         17  YPE=:"SYS_B_01" AND T.OBJECT_TYPE=:"SYS_B_02" AND T.OBJECT_TYPE=:"SYS_B_03" AND T.OBJECT  _TYPE=:"SYS_B_04" AND T.OBJECT_TYPE=:"SYS_B_05" AND T.OBJECT_TYPE=:"SYS_B_06" AND T.OBJE  CT_TYPE=:"SYS_B_07" AND T.OBJECT_TYPE=:"SYS_B_08" AND T.OBJECT_TYPE=:"SYS_B_09" AND T.OB  JECT_TYPE=:"SYS_B_10" AND T.OBJECT_TYPE=:"SYS_B_11" AND T.OBJECT_TYPE=:"SYS_B_12" AND T.  OBJECT_TYPE=:"SYS_B_13"    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,A.PLAN_HASH_VALUE FROM V$SQL A WHERE A.SQL_ID='65ggkpkp6n7mq';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE  ------------- ------------ ---------- ----------- - - - ---------------  65ggkpkp6n7mq            0          2         309 Y N N      2878087074  65ggkpkp6n7mq            1          8        4176 Y Y Y      3865303624  65ggkpkp6n7mq            2          7          21 Y Y Y      2878087074    LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='65ggkpkp6n7mq' ORDER BY CHILD_NUMBER;    ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH  ---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------  0000000095FF3818 2859081334 65ggkpkp6n7mq            1 =SYS_B_00                                         0 0.711697   0.869852  0000000095FF3818 2859081334 65ggkpkp6n7mq            2 =SYS_B_00                                         0 0.000023   0.000028    LHR@orclasm > SELECT * FROM V$SQL_CS_STATISTICS D WHERE D.SQL_ID='65ggkpkp6n7mq' ORDER BY D.CHILD_NUMBER;    ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME  ---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------  0000000095FF3818 2859081334 65ggkpkp6n7mq            0            41778157 Y          1              5          54          0  0000000095FF3818 2859081334 65ggkpkp6n7mq            1          4286870935 Y          1         123637         522          0  0000000095FF3818 2859081334 65ggkpkp6n7mq            2            41778157 Y          1              5           3          0  

我們再來看看帶15個綁定變量的情形：

LHR@orclasm > alter system flush shared_pool;    System altered.    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T    2  WHERE T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    3  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    4  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    5  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    6  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    7  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    8  AND T.OBJECT_TYPE='CLUSTER' AND T.OBJECT_TYPE='CLUSTER'    9  AND T.OBJECT_TYPE='CLUSTER';      COUNT(*)  ----------           2    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T    2  WHERE T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    3  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    4  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    5  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    6  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    7  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    8  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    9  AND T.OBJECT_TYPE='TABLE';      COUNT(*)  ----------       61818    LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T    2  WHERE T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    3  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    4  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    5  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    6  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    7  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    8  AND T.OBJECT_TYPE='TABLE' AND T.OBJECT_TYPE='TABLE'    9  AND T.OBJECT_TYPE='TABLE';      COUNT(*)  ----------       61818  

依據之前常規游標共享和自適應游標共享的測試結果，可以推斷出如果自適應游標共享對上述SQL不起作用的話，那麼常規游標共享就會起作用，即上述SQL在被執行了三次的情況下應該只會產生一個Child Cursor。

從如下查詢結果可以看到，上述SQL在被Oracle用系統產生的綁定變量替換後，對應記錄的列VERSION_COUNT的值為1，列EXECUTIONS的值為3，這說明上述SQL在被執行了三次的情況下確實只產生了一個Child Cursor，即當目標SQL中的綁定變量的數量超過14時，自適應游標共享確實沒有生效：

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=:"SYS_B_00" AND T.OBJECT_T 6zmk2h81jnava             1          3  YPE=:"SYS_B_01" AND T.OBJECT_TYPE=:"SYS_B_02" AND T.OBJECT_TYPE=:"SYS_B_03" AND T.OBJECT  _TYPE=:"SYS_B_04" AND T.OBJECT_TYPE=:"SYS_B_05" AND T.OBJECT_TYPE=:"SYS_B_06" AND T.OBJE  CT_TYPE=:"SYS_B_07" AND T.OBJECT_TYPE=:"SYS_B_08" AND T.OBJECT_TYPE=:"SYS_B_09" AND T.OB  JECT_TYPE=:"SYS_B_10" AND T.OBJECT_TYPE=:"SYS_B_11" AND T.OBJECT_TYPE=:"SYS_B_12" AND T.  OBJECT_TYPE=:"SYS_B_13" AND T.OBJECT_TYPE=:"SYS_B_14"      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,A.PLAN_HASH_VALUE FROM V$SQL A WHERE A.SQL_ID='6zmk2h81jnava';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE  ------------- ------------ ---------- ----------- - - - ---------------  6zmk2h81jnava            0          3         568 N N Y      2878087074  

至此，我們己經介紹完Oracle數據庫中與自適應游標共享相關的全部內容。