【DB筆試面試586】在Oracle中，什麼是自適應游標共享（3）？
- 2019 年 10 月 10 日
- 筆記
現在將的值修改為「JAVA CLASS」，然後再次執行目標SQL：
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 > 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 6 7 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='bt8tk3f1tnwcf'; SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE ------------- ------------ ---------- ----------- - - - --------------- bt8tk3f1tnwcf 0 2 309 Y N N 3002671579 bt8tk3f1tnwcf 1 1 522 Y Y Y 4256744017 bt8tk3f1tnwcf 2 1 16 Y Y N 3002671579 bt8tk3f1tnwcf 3 1 79 Y Y N 3002671579 bt8tk3f1tnwcf 4 1 74 Y Y N 3002671579 bt8tk3f1tnwcf 5 1 3 Y Y Y 3002671579
從如下查詢結果可以看到目標SQL對應的列VERSION_COUNT的值從之前的5變為了現在的6，列EXECUTIONS的值為7，說明Oracle在第7次執行目標SQL時依然用的是硬解析。從查詢結果可以看到，Oracle此時新生成了一個CHILD_NUMBER為5的Child Cursor，並且把存儲相同執行計劃的CHILD_NUMBER為4的原有Child Cursor標記為非共享。
目標SQL現在的執行計劃為如下所示：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',5,'advanced')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- SQL_ID bt8tk3f1tnwcf, child number 5 ------------------------------------- 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]
從上述顯示內容可以看出，目標SQL現在的執行計划走的還是對索引的索引範圍掃描，確實與CHILD_NUMBER為4的原有Child Cursor中存儲的執行計劃相同。注意，「Peeked Binds」部分的內容為「1 – :X (VARCHAR2(30), CSID=852): 'CLUSTER'」，說明Oracle在硬解析上述SQL的過程中確實再次使用了綁定變量窺探，並且做「窺探」這個動作時看到的綁定變量的輸入值為「CLUSTER」。
對於上述CHILD_NUMBER為5的Child Cursor，綁定變量X的輸入值為「CLUSTER」時對應的記錄數為2，表TI的記錄數為78174，帶入合併後的計算公式：
LHR@orclasm > SELECT ROUND(0.9*(2/78174),6) low,ROUND(1.1*(2/78174),6) HIGH FROM DUAL; LOW HIGH ---------- ---------- 0.000023 0.000028 LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY CHILD_NUMBER; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 =X 0 0.711697 0.869852 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 =X 0 0.035482 0.043367 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 =X 0 0.035482 0.052317 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 =X 0 0.027412 0.052317 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 =X 0 0.000023 0.052317 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 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 3279106319 Y 1 3083 16 0 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 3683986157 Y 1 3719 79 0 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 4071504174 Y 1 2382 74 0 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 821942781 Y 1 3 3 0
從上述計算結果可以看出，現在CHILD_NUMBER為5的Child Cursor對應的可選擇率範圍為[0.00002，0.000028]，根本就不在之前V$SQL_CS_SELECTIVITY中記錄的之內，所以Oracle此時還是得用硬解析。和之前一樣，Oracle現在也得做Cursor合併。只不過這次是擴展新Child Cursor對應的可選擇率範圍的上限。CHILD_NUMBER為4的原有Child Cursor對應的可選擇率範圍為[0.027412，0.052317]，CHILD_NUMBER為5的新Child Cursor對應的可選擇率範圍為[0.000023，0.000028]，而0.052317是大於0.000028的，所以這裡Oracle對新Child Cursor的可選擇率範圍的上限做了擴展，擴展後該Child Cursor對應的可選擇率範圍就變成[0.000023，0.052317]。
上述SQL總共執行了7次，但有6次都是硬解析。究其根本原因，還是因為在自適應共享游標被啟用的前提條件下，當已經被標記為Bind Aware的Child Cursor所對應的目標SQL再次被執行時，Oracle會根據當前傳入的綁定變量值所對應的謂詞條件的可選擇率，來決定該SQL此時的執行是用硬解析還是用軟解析/軟軟解析。如果當前傳入的綁定變量值所在的謂詞條件的可選擇率處於該SQL之前硬解析時同名謂詞條件在V$SQL_CS_STATISTICS中記錄的可選擇率範圍之內，則此時Oracle就會使用軟解析/軟軟解析，反之則是硬解析。上述SQL從第4次到第7次的連續4次執行時，對應的謂詞條件的可選擇率範圍均不在之前V$SQL_CS_SELECTIVITY中記錄的相關舊Child Cursor對應的可選擇率範圍之內，所以這4次執行時Oracle都被迫使用硬解析。
在現在這種狀況下，如何才能讓目標SQL再次執行時使用軟解析/軟軟解析呢？很簡單，只需要綁定變量X賦值後其對應謂詞條件的可選擇率的範圍落在V$SQL_CS_SELECTIVITY中記錄的區間[0.711697，0.869852]或[0.000023，0.052317]內就可以了。
現在將的值修改為「VIEW」，然後再次執行目標SQL：
LHR@orclasm > EXEC :X :='VIEW'; PL/SQL procedure successfully completed. LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X; COUNT(*) ---------- 1231 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 6 8 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='bt8tk3f1tnwcf'; SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE ------------- ------------ ---------- ----------- - - - --------------- bt8tk3f1tnwcf 0 2 309 Y N N 3002671579 bt8tk3f1tnwcf 1 1 522 Y Y Y 4256744017 bt8tk3f1tnwcf 2 1 16 Y Y N 3002671579 bt8tk3f1tnwcf 3 1 79 Y Y N 3002671579 bt8tk3f1tnwcf 4 1 74 Y Y N 3002671579 bt8tk3f1tnwcf 5 2 18 Y Y Y 3002671579
從如下查詢結果可以看到目標SQL對應的列VERSION_COUNT的值還是保持之前的6不變，列EXECUTIONS的值從之前的7變為現在的8，說明Oracle在第8次執行目標SQL時確實用的是軟解析/軟軟解析。CHILD_NUMBER為5的Child Cursor中的EXECUTIONS列的值從1變為了2，說明目標SQL確實重用的是CHILD_NUMBER為5的Child Cursor中的解析樹和執行計劃。
目標SQL現在的執行計劃為如下所示：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',5,'advanced')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- SQL_ID bt8tk3f1tnwcf, child number 5 ------------------------------------- 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]
從上述顯示內容可以看出，目標SQL現在的執行計划走的還是對索引的索引範圍掃描，確實與CHILD_NUMBER為4的原有Child Cursor中存儲的執行計劃相同。注意，「Peeked Binds」部分的內容為「1 – :X (VARCHAR2(30), CSID=852): 'CLUSTER'」，說明Oracle確實沿用了之前做「窺探」操作時綁定變量X的輸入值為「CLUSTER」所對應的執行計劃。
對於上述CHILD_NUMBER為5的Child Cursor，綁定變量X的輸入值為「VIEW」時對應的記錄數為1231，表TI的記錄數為78174，帶入合併後的計算公式：
LHR@orclasm > SELECT ROUND(0.9*(1231/78174),6) low,ROUND(1.1*(1231/78174),6) HIGH FROM DUAL; LOW HIGH ---------- ---------- 0.014172 0.017322 LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY CHILD_NUMBER; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 =X 0 0.711697 0.869852 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 =X 0 0.035482 0.043367 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 =X 0 0.035482 0.052317 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 =X 0 0.027412 0.052317 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 =X 0 0.000023 0.052317 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 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 3279106319 Y 1 3083 16 0 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 3683986157 Y 1 3719 79 0 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 4071504174 Y 1 2382 74 0 00000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 821942781 Y 1 3 3 0 LHR@orclasm > SELECT D.SID,D.SQL_ID,D.CURSOR_TYPE,D.SQL_TEXT FROM V$OPEN_CURSOR D WHERE D.SQL_ID='bt8tk3f1tnwcf'; SID SQL_ID CURSOR_TYPE SQL_TEXT ---------- ------------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------- 33 bt8tk3f1tnwcf DICTIONARY LOOKUP CURSOR CACHED SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYP LHR@orclasm > SELECT D.SQL_ID,D.CHILD_NUMBER,D.BIND_EQUIV_FAILURE FROM v$sql_shared_cursor d WHERE d.SQL_ID='bt8tk3f1tnwcf'; SQL_ID CHILD_NUMBER B ------------- ------------ - bt8tk3f1tnwcf 0 N bt8tk3f1tnwcf 1 Y bt8tk3f1tnwcf 2 Y bt8tk3f1tnwcf 3 Y bt8tk3f1tnwcf 4 Y bt8tk3f1tnwcf 5 Y LHR@orclasm >
