Oracle遊標共享（Cursor Sharing）--常規遊標共享和自適應遊標共享（ACS）
Oracle遊標共享（Cursor Sharing）--常規遊標共享和自適應遊標共享（ACS）
遊標共享（Cursor Sharing）是指Shared Cursor間的共享，其實就是重用儲存在Child Cursor中的解析樹和執行計劃而不用從頭開始做硬解析的動作。特別對於除SQL文字中對應的輸入值不同外，其它部分都一模一樣的目標SQL更應該實現遊標共享，而使用繫結變數就可以實現遊標共享。
很多OLTP型別的應用系統的開發人員在開發階段並未意識到硬解析的危害，所以也沒有使用繫結變數，等到系統上線後才發現問題。此時如果要使用繫結變數，則意味著絕大多數SQL都得改寫，這個代價就太大了，所以Oracle引入了常規遊標共享。
即使應用系統在開發階段使用了繫結變數，但在預設情況下也會受到繫結變數窺探的影響。繫結變數窺探的副作用在於，一旦啟用（預設情況下繫結變數窺探就已經被啟用），使用了繫結變數的目標SQL就只會沿用之前硬解析時所產生的解析樹和執行計劃，即使這種沿用完全不適合當前的情形，為了解決這個問題，Oracle引入了自適應遊標共享。
先介紹一下與本小節相關的幾個概念：
?安全的謂詞條件是指如果一個謂詞條件所在的目標SQL的執行計劃並不隨該謂詞條件的輸入值的變化而變化，那麼該謂詞條件就是安全的。比如，對於主鍵列施加等值查詢的謂詞條件，無論傳入的主鍵值是什麼，其執行計劃都會是固定的，不會變化。
?不安全的謂詞條件是指如果目標SQL的執行計劃可能會隨著謂詞條件的輸入值的不同而發生變化，那麼該謂詞條件就是一個不安全的謂詞條件。Oracle資料庫中典型的不安全的謂詞條件有範圍查詢（使用了>、>=、<、<=、BETWEEN的謂詞條件），使用了帶萬用字元（％）的LIKE，以及對有直方圖統計資訊的目標列施加的等值查詢等。
?同一型別SQL是指除SQL文字中對應的輸入值不同外，其它部分都一模一樣的目標SQL，例如，“SELECT ENAME FROM EMP WHERE EMPNO=7369”和“SELECT ENAME FROM EMP WHERE EMPNO=7370”就是同一型別的SQL。
下面分別來介紹常規遊標共享和自適應遊標共享這兩個方面。
1、常規遊標共享
常規遊標共享是在Oracle 8i中引入的。常規遊標共享可以做到既有效降低系統硬解析的數量，又對應用透明，即常規遊標共享可以做到在應用不改一行程式碼的情況下，使那些僅僅是SQL文字中的WHERE條件或者INSERT語句的VALUES子句中的具體輸入值不同的目標SQL彼此之間共享解析樹和執行計劃。當開啟了常規遊標共享後，Oracle在實際解析目標SQL之前，會先用系統產生的繫結變數來替換目標SQL的SQL文字中WHERE條件或者INSERT中的VALUES子句中的具體輸入值，這樣替換後實際執行的SQL就己經是使用了繫結變數的改寫後的等價SQL。Oracle資料庫裡系統產生的繫結變數的命名規則是“:"SYS_B_n"(n=0，1，2，......）”。例如，原目標SQL為“SELECT ENAME FROM EMP WHERE EMPNO=7369”，如果開啟了常規遊標共享，那麼Oracle做替換後的等價改寫形式就是“SELECT ENAME FROM EMP WHERE EMPNO=:"SYS_B_0"”。
Oracle資料庫中的常規遊標共享受引數CURSOR_SHARING的控制，其值可以被設定為EXACT、SIMILAR或FORCE，它們各自的含義如下所示：
?EXACT 該引數是CURSOR_SHARING的預設值，表示Oracle不會用系統產生的繫結變數來替換目標SQL的SQL文字中WHERE條件或者INSERT語句的VALUES子句中的具體輸入值。
?SIMILAR 表示Oracle會用系統產生的繫結變數來替換目標SQL的SQL文字中WHERE條件或者INSERT語句的VALUES子句中的具體輸入值。在這種情況下，Oracle只會對那些它認為是安全的謂詞條件在替換後重用解析樹和執行計劃，對於它認為的不安全的謂詞條件，即便用系統產生的繫結變數替換後的SQL文字是一模一樣的，對於每一個不同的輸入值，Oracle都會執行一次硬解析，即此時會出現一個Parent Cursor下掛一堆Child Cursor的現象，而這些Child Cursor中儲存的解析樹和執行計劃很可能是一樣的（需要注意的是，因為自適應遊標共享的引入，這種行為不再適用於Oracle 11g及其後續的版本）。在Oracle 12c以及後續的版本中SIMILAR將過時，不再被繼續支援。因為當CURSOR_SHARING設成SIMILAR後會帶來一系列的問題，並且有太多與SIMILAR相關的Bug。
?FORCE 和SIMILAR一樣，FORCE表示Oracle會用系統產生的繫結變數來替換目標SQL的SQL文字中WHERE條件或者INSERT語句的VALUES子句中的具體輸入值。但和SIMILAR不同的是，當CURSOR_SHARING的值為FORCE時，替換後同一型別的SQL總是會無條件地重用之前硬解析時的解析樹和執行計劃（需要注意的是，因為自適應遊標共享的引入，這種行為不再適用於Oracle 11g及其後續的版本）。
下面給出一個與常規遊標共享有關的示例（資料庫版本為10.2.0.1）：
準備相關的表並收集統計資訊：
CREATE TABLE T_CS_20170610 AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX IDX_OBJ_LHR ON T_CS_20170610(OBJECT_ID);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t_cs_20170610',ESTIMATE_PERCENT => 100,CASCADE => TRUE,METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE => FALSE);
查詢：
SYS@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=0;
COUNT(1)
----------
0
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=1;
COUNT(1)
----------
0
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=1 gbkpakaxfmbm4 1 1
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=0 f9uyh6hyf7kcc 1 1
現在CURSOR_SHARING的值為EXACT，所以Oracle不會用系統產生的繫結變數來替換上述SQL的WHERE條件中的輸入值，而上述兩個SQL的WHERE條件中的輸入值並不相同（一個是0，另一個是1），即意味著這兩個SQL在執行時均會使用硬解析。
對於上述兩個SQL而言，其謂詞條件均為“OBJECT_ID=XXX”，這是一個等值查詢條件，同時目標列OBJECT_ID上沒有直方圖統計資訊，所以該謂詞條件是一個安全的謂詞條件。也就是說，如果把CURSOR_SHARING的值改為SIMILAR後再次執行這兩個SQL，那麼Oracle就會用系統產生的繫結變數來替換上述謂詞條件中的輸入值，這意味著當執行“SELECT COUNT(1) FROM T_CS_20170610 WHERE OBJECT_ID=1”時，Oracle會沿用之前執行“SELECT COUNT(1) FROM T_CS_20170610 WHERE OBJECT_ID=0”所對應的解析樹和執行計劃。
下面把CURSOR_SHARING修改為SIMILAR：
SYS@ora10g> ALTER SESSION SET CURSOR_SHARING='SIMILAR';
Session altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=0;
COUNT(*)
----------
0
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=1;
COUNT(*)
----------
0
SYS@ora10g> col sql_text format a80
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=:"SYS_B_0" bgzgahgcxyss7 1 2
注意，列VERSION_COUNT的值為1，列EXECUTIONS的值為2，這說明在目標SQL的謂詞條件是安全的謂詞條件，且CURSOR_SHARING的值為SIMILAR的前提條件下，Oracle確實會重用之前硬解析時所對應的解析樹和執行計劃。
由於上述兩個SQL的謂詞條件是安全的謂詞條件，因此把CURSOR_SHARING的值改為SIMILAR或者FORCE並沒有什麼區別，即如果把CURSOR_SHARING的值改為FORCE後再次執行這兩個SQL，所得到的結果應該和CURSOR SHARING的值為SIMILAR時一樣。
來驗證一下把CURSOR_SHARING的值改為FORCE，並再次執行這兩個SQL：
SYS@ora10g> ALTER SESSION SET CURSOR_SHARING='FORCE';
Session altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=0;
COUNT(*)
----------
0
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=1;
COUNT(*)
----------
0
SYS@ora10g> col sql_text format a80
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID=:"SYS_B_0" bgzgahgcxyss7 1 2
現在再來看在不安全的謂詞條件下當CURSOR SHARING的值分別為EXACT、SIMILAR和FORCE時的對比。還是先來看CURSOR_SHARING的值為EXACT的情形：
SYS@ora10g> ALTER SESSION SET CURSOR_SHARING=EXACT;
Session altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 0 AND 1;
COUNT(*)
----------
0
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2;
COUNT(*)
----------
1
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2 g6ygwtg4482r3 1 1
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 0 AND 1 7b5sugy5n62gq 1 1
下面把CURSOR_SHARING修改為SIMILAR：
SYS@ora10g> ALTER SESSION SET CURSOR_SHARING=SIMILAR;
Session altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 0 AND 1;
COUNT(*)
----------
0
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2;
COUNT(*)
----------
1
--這裡若不能使用常規遊標共享，則可以多清理幾次共享池，另外，執行SQL查詢時中間間隔稍微長一點。
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND :"SYS_B_1" 21371b4zdvrkg 2 2
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,EXECUTIONS FROM V$SQL D WHERE SQL_ID = '21371b4zdvrkg';
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,D.CHILD_NUMBER,EXECUTIONS,PLAN_HASH_VALUE FROM V$SQL D WHERE SQL_ID = '21371b4zdvrkg';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
---------------------------------------------------------------------------------------- ------------- ------------ ---------- ---------------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND :"SYS_B_1" 21371b4zdvrkg 0 1 3299589416
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND :"SYS_B_1" 21371b4zdvrkg 1 1 3299589416
上述兩個Child Cursor所對應的列PLAN_HASH_VALUE的值均為3299589416，說明雖然這裡確實產生了兩個Child Cursor，但它們儲存的執行計劃卻是相同的。從如下顯示內容可以看到，這兩個Child Cursor中儲存的執行計劃確實是相同的（走的均是對索引IDX_OBJ_LHR的索引範圍掃描）：
SYS@ora10g> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('21371b4zdvrkg',0,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------
SQL_ID 21371b4zdvrkg, child number 0
-------------------------------------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND
:"SYS_B_1"
Plan hash value: 3299589416
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_OBJ_LHR | 1 | 5 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T_CS_20170610@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T_CS_20170610"@"SEL$1" ("T_CS_20170610"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (NUMBER): 0
2 - :SYS_B_1 (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:SYS_B_0<=:SYS_B_1)
3 - access("OBJECT_ID">=:SYS_B_0 AND "OBJECT_ID"<=:SYS_B_1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
52 rows selected.
SYS@ora10g>
SYS@ora10g> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('21371b4zdvrkg',1,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL_ID 21371b4zdvrkg, child number 1
-------------------------------------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND
:"SYS_B_1"
Plan hash value: 3299589416
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_OBJ_LHR | 1 | 5 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T_CS_20170610@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T_CS_20170610"@"SEL$1" ("T_CS_20170610"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (NUMBER): 1
2 - :SYS_B_1 (NUMBER): 2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:SYS_B_0<=:SYS_B_1)
3 - access("OBJECT_ID">=:SYS_B_0 AND "OBJECT_ID"<=:SYS_B_1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
52 rows selected.
這是很不合理的，也是CURSOR_SHARING的值被設為SIMILAR後的主要弊端之一。我們將CURSOR_SHARING的值設為SIMILAR的目的，是想在應用不改一行程式碼的情形下，使那些僅僅是SQL文字中的WHERE條件或者VALUES子句（適用於INSERT語句）的具體輸入值不同的目標SQL彼此之間共享解析樹和執行計劃，以達到有效降低系統硬解析數量的目的。但在Oracle l1g之前，CURSOR_SHARING的值被設為SIMILAR後你可能會發現這麼做的效果有限，系統硬解析的數量並未得到大幅度的降低，而且會出現一個Parent Cursor下掛一堆Child Cursor的現象，而這些Child Cursor中儲存的解析樹和執行計劃很可能是一樣的。以上述兩個SQL為例，在當前條件下，“SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 0 AND 1”和“SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2”的執行計劃是一樣的，顯然它們本應共享解析樹和執行計劃，但就是因為CURSOR_SHARING的值被設為SIMILAR，外加這兩個SQL使用的是不安全的謂詞條件，所以就導致Oracle在執行它們時均使用了硬解析。
在Oracle 10g及其後續的版本中，Oracle會自動收集直方圖統計資訊，這意味著在Oracle 10g及其後續的版本中出現不安全的謂詞條件的概率要大大高於Oracle 10g之前的版本，所以在Oracle 10g裡不要將CURSOR_SHARING的值設成SIMILAR，因為很可能達不到在不改一行應用程式碼的情形下有效降低系統硬解析數量的目的（更何況還可能會因此而引入一堆Bug).
在Oracle 11g裡也不要將CURSOR SHARING的值設成SIMILAR，因為Oracle 11g裡自適應遊標共享已經被預設啟用了，在自適應遊標共享被啟用的情形下，Oracle並不推薦將CURSOR_SHARING的值設為SIMILAR（參見MOS上的文章"FAQ:Adaptive Cursor Shanng(ACS)Frequently Asked Questions(ID 1518681.1)”。
再次執行SQL語句“SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2;”：
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2;
COUNT(*)
----------
1
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND :"SYS_B_1" 21371b4zdvrkg 2 3
從查詢結果可以看到列VERSION_COUNT的值還是2，但列EXECUTIONS的值己經從之前的2變為現在的3，說明在目標SQL的謂詞條件是不安全的謂詞條件，且CURSOR_SHARING的值為SIMILAR的前提條件下，只有針對該謂詞條件的當前輸入值和之前的輸入值完全相同時，Oracle才會重用之前該輸入值所對應的解析樹和執行計劃.
上述兩個SQL的謂詞條件雖然是不安全的，但不管是“安全的謂詞條件”還是“不安全的謂詞條件”，當把CURSOR_SHARING的值設為FORCE後，Oracle總是會無條件重用目標SQL之前硬解析時的解析樹和執行計劃（僅適用於Oracle 11g之前的版本）。所以如果把CURSOR_SHARING的值設為FORCE後再次執行這兩個SQL，那麼得到的結果應和之前CURSOR_SHARING的值為SIMILAR時不同。
SYS@ora10g> ALTER SESSION SET CURSOR_SHARING=FORCE;
Session altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 0 AND 1;
COUNT(*)
----------
0
SYS@ora10g> SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN 1 AND 2;
COUNT(*)
----------
1
SYS@ora10g> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_CS_20170610 WHERE OBJECT_ID BETWEEN :"SYS_B_0" AND :"SYS_B_1" 21371b4zdvrkg 1 2
SYS@ora10g>
上述顯示內容中列VERSION_COUNT的值為1，列EXECUTIONS的值為2，說明即使目標SQL的謂詞條件是不安全的，只要CURSOR_SHARING的值為FORCE，則Oracle就會無條件地重用之前硬解析時對應的解析樹和執行計劃（僅適用於Oracle l1g之前的版本）。
從上述整個測試過程可以得到如下結論。
?SIMILAR是一個即將過時的值，它有太多的副作用，無論什麼時候都不要將CURSOR_SHARING的值設為SIMILAR
?如果想在不改一行應用程式碼的情況下，使那些僅僅是SQL文字中的WHERE條件或者VALUES子句（適用於INSERT語句）中的具體輸入值不同的目標SQL共享解析樹和執行計劃，以達到有效降低系統硬解析數量的目的，那就將CURSOR_SHARING的值設成FORCE吧，雖然這不是最理想的方案（最理想的方案當然還是修改應用的程式碼，在SQL語句裡使用繫結變數，並且儘可能使用批量繫結），但這也許是最省事的方案。
2、自適應遊標共享
繫結變數窺探的副作用就在於，使用了繫結變數的目標SQL只會沿用之前硬解析時所產生的解析樹和執行計劃，即使這種沿用完全不適合於當前的情形。在Oracle 10g及其後續的版本中，Oracle會自動收集直方圖統計資訊，這意味著與之前的版本相比，在Oracle 10g及其後續的版本中Oracle有更大的概率會知道目標列實際資料的分佈情況，也就是說繫結變數窺探的副作用將會更加明顯。當Oracle執行繫結變數窺探操作時繫結變數所對應的輸入值是否具有代表性就至關重要了（這裡“代表性”是指該輸入值所對應的執行計劃和該SQL在大多數情況下的執行計劃相同），因為這會直接決定此目標SQL在硬解析時所選擇的執行計劃，進而決定後續以軟解析/軟軟解析重複執行時所沿用的執行計劃。
為了解決上述繫結變數窺探所帶來的問題，Oracle在l1g中引入了自適應遊標共享（Adaptive Cursor Sharing，ACS）。自適應遊標共享可以讓使用了繫結變數的目標SQL在啟用了繫結變數窺探的前提條件下，不再只沿用之前硬解析時所產生的解析樹和執行計劃，也就是說自適應遊標共享可以在啟用了繫結變數窺探的前提條件下，讓目標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：
?啟用了繫結變數窺探。
?該SQL使用了繫結變數（不管是該SQL自帶的繫結變數，還是開啟常規遊標共享後系統產生的繫結變數）。
?該SQL使用的是不安全的謂詞條件（例如範圍查詢，目標列上有直方圖統計資訊的等值查詢等）。
自適應遊標共享要做的第二件事情就是將目標SQL所對應的Child Cursor標記為Bind Aware。Bind Aware是指Oracle已經確定某個含繫結變數的目標SQL的執行計劃會隨著所傳入的繫結變數輸入值的變化而變化。
當滿足如下兩個條件時，目標SQL所對應的Child Cursor就會被Oracle標記為BindAware：
?該SQL所對應的Child Cursor在之前已經被標記為Bind Sensitive。
?該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也會在第一時間被age out出Shared Pool。
與自適應遊標共享相關的有兩個重要檢視，分別是V$SQL_CS_STATISTICS和V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS用於顯示指定Child Cursor中儲存的runtime統計資訊。
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 Awareo
（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在VSSQL中對應記錄的列IS SHAREABLE的值也會從Y變為N)，在把原有Child cursor標記為非共享的同時，Oracle還會對新生成的Child Cursor執行一個Cursor合併的過程（這裡Cursor合併的含義是指Oracle會合並儲存相同執行計劃的原有Child Cursor和新生成的Child Cursor)：如果是軟解析/軟軟解析，則Oracle會重用相關Child Cursor中儲存的解析樹和執行計劃。
下面給出一個自適應遊標的示例：
資料庫版本為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]。
另外，在目標列有Frequency型別直方圖的前提條件下，如果對目標列施加等值查詢條件，且該查詢條件的輸入值等於該列的某個實際值時，則該謂詞條件的可選擇率的計算公式為如下所示：
selectivity=BucketSize/NUM_ROWS
其中，BucketSize表示目標列的某個實際值所對應的記錄數。
合併上述計算公式可以得出，對於表TI而言，在當前情形下V$SQL_CS_SELECTIVITY中記錄的可選擇率的範圍的計算公式為[0.9*BucketSize/NUM_ROWS，1.1*BucketSize/NUM_ROWS]。
對於上述CHILD NUMBER為1的Child Cursor而言，繫結變數攴的輸入值為“TABLE”時對應的記錄數為61818（即BucketSize的值是61818），表Tl的記錄數為78174（即NUM_ROWS的值為78174），將61818和78174帶入上述合併後的計算公式：
LHR@orclasm > SELECT d.NUM_ROWS FROM dba_tables d WHERE d.TABLE_NAME='T_ACS_20170611_LHR';
NUM_ROWS
----------
78174
LHR@orclasm > SELECT ROUND(0.9*(61818/78174),6) low,ROUND(1.1*(61818/78174),6) HIGH FROM DUAL;
LOW HIGH
---------- ----------
0.711697 0.869852
從上述計算結果可以看出，可選擇率範圍和之前從VSSQL_CS_SELECTIVITY中查到的結果完全一致。
現在將X的值修改為“INDEX”：
LHR@orclasm > EXEC :X :='INDEX';
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
COUNT(*)
----------
3082
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 3 4
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 Y 3002671579
LHR@orclasm >
從如下查詢結果可以看到，目標SQL對應的列VERSION_COUNT的值從之前的2變為現在的3，列EXECUTIONS的值為4，說明Oracle在第4次執行該SQL時依然用的是硬解析。目標SQL多了一個CHILD_NUMBER為2的新Child Cursor，且該Child Cursor對應的IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE的值均為Y，但是這個新Child Cursor和CHILD_NUMBER為0的原有Child Cursor的對應PLAN_HASH_VALUE的值均為3002671579（說明這兩個Child Cursor中儲存的執行計劃是相同的），而且CHILD_NUMBER為0的原有Child Cursor對應IS_SHAREABLE的值己經從之前的Y變為現在的N。
這些變化表明，對於標記為Bind Aware的Child Cursor所對應的目標SQL，當該SQL後續再次被執行時如果對應的是硬解析，且本次硬解析所產生的執行計劃和原有Child Cursor中儲存的執行計劃相同，則Oracle此時除了會新生成一個Child Cursor之外，還會把儲存相同執行計劃的原有Child Cursor標記為非共享（把原有Child Cursor在V$SQL中對應記錄的列IS SHAREABLE的值從Y改為N）。
目標SQL現在的執行計劃如下所示：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',2,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID bt8tk3f1tnwcf, child number 2
-------------------------------------
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 | | | | 15 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 3082 | 21574 | 15 (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): 'INDEX'
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.
從上述顯示內容可以看出，目標SQL現在的執行計劃是走對索引IDX_ACS_OBJID_LHR的索引範圍掃描，確實與CHILD_NUMBER為0的原有Child Cursor中儲存的執行計劃相同。注意到“Peeked Binds”部分的內容為“1 - :X (VARCHAR2(30), CSID=852): 'INDEX'”，這說明Oracle在硬解析目標SQL的過程中確實再次使用了繫結變數窺探，而且做“窺探”這個動作時看到的繫結變數的輸入值為“INDEX”。
現在的問題是，既然Oracle此時選擇的執行計劃與原有Child Cursor中儲存的執行計劃相同，為什麼不直接沿用原先的執行計劃而是還得再做一次硬解析呢？
在介紹自適應遊標共享的整體執行流程時曾經提到過：對於標記為Bind Aware的Child Cursor所對應的目標SQL，當該SQL再次被執行時，Oracle就會根據當前傳入的繫結變數值所對應的謂詞條件的可選擇率，來決定該SQL此時的執行是用硬解析還是用軟解析/軟軟解析。
對於上述CHILD_NUMBER為2的Child Cursor，繫結變數攴的輸入值為“INDEX”時對應的記錄數為3082，表TI的記錄數為78174，帶入合併後的計算公式：
LHR@orclasm > SELECT ROUND(0.9*(3082/78174),6) low,ROUND(1.1*(3082/78174),6) HIGH FROM DUAL;
LOW HIGH
---------- ----------
0.035482 0.043367
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
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
LHR@orclasm >
從上述計算結果可以看出，現在CHILD_NUMBER為2的Child Cursor對應的可選擇率的範圍為[0.035482，0.043367]，根本就不在之前VSSQL_CS_SELECTIVITY中記錄的CHILD_NUMBER為1的Child Cursor所在的可選擇率的範圍[0.711697，0.869852]之內，所以Oracle此時還是得用硬解析。
由於上述CHILD_NUMBER為2的Child Cursor也是Bind Aware的，所以其對應的可選擇率也被記錄在了VSSQL_CS_SELECTIVITY中。
注意，這裡不存在Cursor合併的過程，因為Cursor合併是指Oracle會合並儲存相同執行計劃的原有Child Cursor和新生成的Child Cursor。這裡CHILD_NUMBER為1的Child Cursor儲存的執行計劃走的是對索引的索引快速全掃描，而CHILD_NUMBER為2的Child Cursor儲存的執行計劃則是走的索引範圍掃描，即它們各自儲存的執行計劃是不相同的，所以此時Oracle不能對它們做Cursor合併。
現在將x的值修改為“SYNONYM”：
LHR@orclasm > EXEC :X :='SYNONYM';
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
COUNT(*)
----------
3718
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 4 5
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 Y 3002671579
LHR@orclasm >
從查詢結果可以看到目標SQL對應的列VERSION_COUNT的值從之前的3變為現在的4，列EXECUTIONS的值為5，說明Oracle在第5次執行目標SQL時依然用的是硬解析。從上述查詢結果可以看到，Oracle此時新生成了一個CHILD_NUMBER為3的Child Cursor，並且把儲存相同執行計劃的CHILD NUMBER為2的原有Child Cursor標記為非共享。
該SQL現在的執行計劃為如下所示：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',3,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bt8tk3f1tnwcf, child number 3
-------------------------------------
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 | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 3718 | 26026 | 18 (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): 'SYNONYM'
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為2的原有Child Cursor中儲存的執行計劃相同。注意到“Peeked Binds”部分的內容為“1 - :X (VARCHAR2(30), CSID=852): 'SYNONYM'”，這說明Oracle在硬解析該SQL的過程中確實再次使用了繫結變數窺探，並且做“窺探”這個動作時看到的繫結變數攴的輸入值為“SYNONYM”。
對於上述CHILD_NUMBER為3的Child Cursor，繫結變數X的輸入值為“SYNONYM”時對應的記錄數為3718，表TI的記錄數為78174，將值帶入前面合併後的計算公式：
LHR@orclasm > SELECT ROUND(0.9*(3718/78174),6) low,ROUND(1.1*(3718/78174),6) HIGH FROM DUAL;
LOW HIGH
---------- ----------
0.042805 0.052317
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
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
從上述計算結果可以看出，現在CHILD_NUMBER為3的Child Cursor對應的可選擇率範圍為[0.042805，0.052317]，根本就不在之前V$SQL_CS_SELECTIVITY中記錄的CHILD NUMBER為1的Child Cursor對應的可選擇率範圍[0.711697，0.869852]之內，也不在CHILD_NUMBER為2的Child Cursor對應的可選擇率範圍[0.035482，0.052317]之內，所以Oracle此時還是得用硬解析。
注意，和之前有所不同的是，現在Oracle就能做cursor合併了。因為現在CHILD_NUMBER為2的原有Child Cursor和CHILD_NUMBER為3的新Child Cursor儲存的執行計劃都是走對索引的索引範圍掃描，即它們各自儲存的執行計劃是相同的，所以此時Oracle就可以對它們做Cursor合併。
Cursor合併的過程也包括對各自所對應的可選擇率範圍的合併，合併的原則就是擴充套件，即要麼擴充套件新Child cursor對應的可選擇率範圍的下限，要麼擴充套件新Child Cursor對應的可選擇率範圍的上限。原有Child Cursor對應的可選擇率範圍是[0.035482，0.052317]，新Child Cursor對應的可選擇率範圍為[0.042805，0.052317]，而0.035482是小於0.042805的，所以這裡Oracle對新Child Cursor的可選擇率範圍的下限做了擴充套件，擴充套件後該Child Cursor對應的可選擇率範圍就變成了[0.035482，0.052317]，即從V$SQL_CS_SELECTIVITY查詢出來的CHILD_NUMBER為3的新Child Cursor的可選擇率範圍。
現在將的值修改為“JAVA CLASS”，然後再次執行目標SQL：
LHR@orclasm > EXEC :X :='JAVA CLASS';
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
COUNT(*)
----------
2381
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 5 6
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 Y 3002671579
從如下查詢結果可以看到目標SQL對應的列VERSION_COUNT的值從之前的4變為了現在的5，列EXECUTIONS的值為6，說明Oracle在第6次執行目標SQL時依然用的是硬解析。從查詢結果可以看到，Oracle此時新生成了一個CHILD_NUMBER為4的Child Cursor，並且把儲存相同執行計劃的CHILD NUMBER為3的原有Child Cursor標記為非共享。
目標SQL現在的執行計劃為如下所示：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',4,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID bt8tk3f1tnwcf, child number 4
-------------------------------------
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 | | | | 12 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2381 | 16667 | 12 (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): 'JAVA CLASS'
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.
從上述顯示內容可以看出，目標SQL現在的執行計劃走的還是對索引的索引範圍掃描，確實與CHILD_NUMBER為3的原有Child Cursor中儲存的執行計劃相同。注意，“Peeked Binds”部分的內容為“1 - :X (VARCHAR2(30), CSID=852): 'JAVA CLASS'”，說明Oracle在硬解析上述SQL的過程中確實再次使用了繫結變數窺探，並且做“窺探”這個動作時看到的繫結變數的輸入值為"JAVA CLASS”。
對於上述CHILD_NUMBER為4的Child Cursor，繫結變數X的輸入值為“JAVA CLASS”時對應的記錄數為2381，表TI的記錄數為78174，帶入合併後的計算公式：
LHR@orclasm > SELECT ROUND(0.9*(2381/78174),6) low,ROUND(1.1*(2381/78174),6) HIGH FROM DUAL;
LOW HIGH
---------- ----------
0.027412 0.033503
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
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
從上述計算結果可以看出，現在CHILD_NUMBER為4的Child Cursor對應的可選擇率範圍為[0.027412，0.033503]，根本就不在之前V$SQL_CS_SELECTIVITY中記錄的之內，所以Oracle此時還是得用硬解析。和之前一樣，Oracle現在也得做Cursor合併。只不過這次是擴充套件新Child Cursor對應的可選擇率範圍的上限。CHILD_NUMBER為3的原有Child Cursor對應的可選擇率範圍為[0.035482，0.052317]，CHILD_NUMBER為4的新Child Cursor對應的可選擇率範圍為[0.027412，0.033503]，而0.052317是大於0.033503的，所以這裡Oracle對新Child Cursor的可選擇率範圍的上限做了擴充套件，擴充套件後該Child Cursor對應的可選擇率範圍就變成[0.027412，0.052317]。
現在將的值修改為“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
從上述計算結果可以看出，現在計算出的可選擇率範圍為[0.014172，0.017322]，在CHILD_NUMBER為5的原有Child Cursor對應的可選擇率範圍[0.000023，0.052317]之內，所以剛才Oracle在執行上述SQL時（即第8次執行目標SQL）用的就是軟解析/軟軟解析，並且此時重用的就是CHILD_NUMBER為5的Child Cursor中儲存的解析樹和執行計劃。
從上述對自適應遊標共享的整個測試過程可以看出，自適應遊標共享雖然在一定程度上緩解了繫結變數窺探所帶來的副作用，但自適應遊標共享並不是完美的，它可能存在如下缺陷。
?可能導致一定數量的額外的硬解析（比如上述目標SQL總共執行了8次，但有6次都是硬解析）。
?可能導致一定數量的額外的Child Cursor掛在同一個Parent Cursor下（比如上述目標SQL總共執行了8次，但產生了6個Child Cursor），這會增加軟解析/軟軟解析時查詢匹配Child Cursor的工作量。
另外，為了儲存這些額外的Child Cursor，Shared P00|在空間方面也會承受額外的壓力（所以當從Oracle 1 Og升級到Oracle 11g時，Oracle會建議適當增加Shared Pool的大小）。
如果因為開啟自適應遊標共享而導致系統產生了過多的Child Cursor，進而導致Shared Pool的空間緊張或者過多的Mutex等待，則可以通過如下任意一種方式來把自適應遊標共享禁掉。
?將隱含引數“_OPTIMIZER_EXTENDED_CURSOR_SHARING”和“_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL”的值均設為NONE，這樣就相當於關閉了可擴充套件遊標共享。一旦可擴充套件遊標共享被禁，所有的Child Cursor都將不能再被標記為Bind Sensitive。而被標記為Bind Sensitive是Child Cursor被後續標記為Bind Aware的前提條件，一旦不能被標記為Bind Sensitive，則後續的Bind Aware就無從談起，也就是說此時自適應遊標共享就相當於被禁掉了。
?將隱含引數“_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
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');
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
8 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資料庫中與自適應遊標共享相關的全部內容。
3、常規遊標共享和自適應遊標共享的聯絡和區別
從嚴格意義上來說，常規遊標共享和自適應遊標共享是各自獨立的，兩者之間沒有必然的聯絡。
常規遊標共享的目的是通過使用系統產生的繫結變數替換目標SQL的SQL文字中的具體輸入值，以達到在不改一行應用程式碼的情況下，使那些僅僅是SQL文字的WHERE條件或者VALUES子句（適用於INSERT語句）中的具體輸入值不同的目標SQL彼此之間共享解析樹和執行計劃。而自適應遊標共享的目的在於通過適時觸發硬解析的動作來緩解繫結變數窺探所帶來的副作用。
在Oracle 11g中，對於使用了繫結變數的目標SQL而言，不管這個繫結變數是該SQL自帶的還是開啟常規遊標共享後系統產生的，只要滿足一定的條件（比如繫結變數窺探被開啟，該SQL中使用的繫結變數的數量不超過14等），自適應遊標共享就能生效。例如，如果引數CURSOR_SHARING的值為EXACT，那麼必須在目標SQL使用自定義的繫結變數後自適應遊標共享才能生效；而當引數CURSOR_SHARING的值為FORCE時，即使目標SQL沒有使用自定義的繫結變數，自適應遊標共享也能生效。
在自適應遊標共享被開啟的情況下，Oracle並不推薦將CURSOR_SHARING的值設為SIMILAR，因為當把CURSOR_SHARING的值設為SIMILAR後，對自適應遊標共享可能有不好的影響，因為SIMILAR本來就是一個即將過時的值，它有太多的副作用，無論什麼時候都不要將CURSOR SHARING的值設為SIMILAR。
Oracle自適應共享遊標
自適應遊標共享Adaptive Cursor Sharing或擴充套件的遊標共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用於解決以前版本中由於繫結變數窺探導致SQL語句無法獲得最佳執行計劃的缺陷，即能夠對效率低下的遊標(子游標)進行自動識別而選擇最佳的執行計劃。本文詳細描述了自適應遊標共享並給出示例。
有關繫結變數窺探請參考：Oracle 繫結變數窺探
有關繫結變數窺探請參考：Oracle 繫結變數窺探
一、示例自適應遊標共享
1、建立演示環境
- SQL> select * from v$version where rownum<2;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- SQL> create table t(id,owner,object_id) as
- 2 select rownum,owner,object_id from all_objects where rownum<=1000;
- SQL> alter table t add constraint t_pk primary key(id);
- SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
- SQL> select count(id),count(distinct id),min(id),max(id) from t;
- COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
- ---------- ----------------- ---------- ----------
- 1000 1000 1 1000
2、使用繫結變數執行SQL語句並獲得首次執行情況
- SQL> var v_id number;
- SQL> exec :v_id:=9;
- SQL> set linesize 180
- SQL> select sum(object_id) from t where id<:v_id;
- SUM(OBJECT_ID)
- --------------
- 2078
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->變數值為9時，使用了正確的執行計劃，且預估的行數也準確
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
- | 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- SQL> col SQL_TEXT format a45 -->下面的語句獲得自適應遊標共享的3個欄位值
- SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
- ------------- --------------------------------------------- ------------ ---------- - - -
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 0 1 Y N Y
3、自適應遊標共享的外在體現
自適應遊標共享主要通過三個欄位來得以體現，即is_bind_sensitive,is_bind_aware,is_shareable。(注:此三個欄位僅在Oracle 11g
中存在)。通過上面從v$sql(v$sqlarea中不存在is_shareable)的查詢可知，三個欄位分別被賦予了不同的值，代表了不同的含義。
is_bind_sensitive(繫結是否敏感)
表示該子游標中是否使用了繫結變數要素，且採用bind peeking方法進行執行計劃生成。如果執行計劃依賴於窺探到的值，此處為Y，
否則為N。
is_bind_aware(繫結是否可知)
表示該子游標是否使用了extended cursor sharing技術，是則為Y，否則為N，如為N，則該遊標將廢棄，不再可用。
is_shareable(是否可共享)
表示該子游標可否被下次軟解析是否可共享使用。可共享則為Y，否則為N，表示該子游標失去了共享價值，按LRU演算法淘汰。
由於該SQL語句為首次執行，因此從v$sql查詢的結果中得知
is_bind_sensitive 為Y值(首次執行，執行了bind peeking)
is_bind_aware 為N值(首次執行，不被extended cursor sharing支援)
is_shareable 為Y值(執行計劃可共享)
4、重新賦值後觀察遊標共享情況
- SQL> exec :v_id:=900;
- SQL> select sum(object_id) from t where id<:v_id;
- SUM(OBJECT_ID)
- --------------
- 1826561
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->此次執行的變數值為900，執行計劃位上次變數為9的執行計劃
- -->此時為非正確的執行計劃，等同於Oracle 9i,10g中的情形
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
- | 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- -->自適應遊標共享的3個欄位值並未發生任何變化
- SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
- ------------- --------------------------------------------- ------------ ---------- - - -
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 0 2 Y N Y
- SQL> select sum(object_id) from t where id<:v_id; -->再次執行變數為900值的SQL語句
- SUM(OBJECT_ID)
- --------------
- 1826561
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
- SQL_ID 7qcp6urqh7d2j, child number 1
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->此時執行計劃較上一次發生了變化，使用了全表掃描,Rows接近於實際值
- -->自適應遊標共享特性得以體現
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- -->自適應遊標共享特性的幾個值發生了變化，生成了新的子游標，其子游標號為1
- SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
- ------------- --------------------------------------------- ------------ ---------- - - -
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 0 2 Y N Y
- 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 1 1 Y Y Y
- SQL> exec :v_id:=800 -->為變數賦於不同的值
- SQL> select sum(object_id) from t where id<:v_id; -->利用新的變數值執行SQL語句
- SUM(OBJECT_ID)
- --------------
- 1548431
- SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID CHILD_NUMBER EXECUTIONS I I I
- ------------- ------------ ---------- - - -
- 7qcp6urqh7d2j 0 2 Y N Y
- 7qcp6urqh7d2j 1 1 Y Y N
- 7qcp6urqh7d2j 2 1 Y Y Y -->生成了新的子游標號為2
- SQL> exec :v_id:=500; -->為變數賦於新值
- SQL> select sum(object_id) from t where id<:v_id; -->利用新的變數值執行SQL語句
- SUM(OBJECT_ID)
- --------------
- 826694
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
- SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
- 2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
- SQL_ID CHILD_NUMBER EXECUTIONS I I I
- ------------- ------------ ---------- - - -
- 7qcp6urqh7d2j 0 2 Y N Y
- 7qcp6urqh7d2j 1 1 Y Y N
- 7qcp6urqh7d2j 2 1 Y Y N -->注意看子游標1,2的is_shareable值為N，表示不可共享
- 7qcp6urqh7d2j 3 1 Y Y Y -->生成了新的子游標號為3，
- -->檢視最終該SQL語句的不同子游標的所有執行計劃
- SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->0號子游標為索引範圍掃描
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
- | 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 1
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->1號子游標為全表掃描，其預估的行數接近實際影響行數的值為900
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 2
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->2號子游標為全表掃描，但其預估的行數接近實際影響行數的值為800
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 800 | 6400 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 3
- -------------------------------------
- select sum(object_id) from t where id<:v_id -->3號子游標為全表掃描，但其預估的行數等於實際影響行數的值499
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS FULL| T | 499 | 3992 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
二、自適應遊標共享的幾個相關檢視
1、v$sql_cs_statistics
用於監控自適應遊標共享的相關統計資訊.下面的查詢中列出了每個子游標的peeking情況,以及執行次數,預處理行數,BUFFER_GETS等
- SQL> select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets
- 2 from v$sql_cs_statistics where sql_id='7qcp6urqh7d2j'
- 3 order by 1;
- CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
- ------------ ------------------- - ---------- -------------- -----------
- 0 1706589901 Y 1 17 69 -->17行，索引範圍掃描
- 1 3116944019 Y 1 900 5 -->900行，全表掃描
- 2 1328865654 Y 1 800 5 -->800行，全表掃描
- 3 1624350242 Y 1 500 5 -->500行，全表掃描
2、v$sql_cs_selectivity
顯示每個子游標的遊標的選擇性範圍。下面的查詢中列出了謂詞，選擇性範圍，列上的選擇性的值
- SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity
- 2 where sql_id='7qcp6urqh7d2j' order by 1;
- CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
- ------------ ------------------ ---------- ---------- ----------
- 1 <V_ID 0 0.809910 0.989890
- 2 <V_ID 0 0.719820 0.989890
- 3 <V_ID 0 0.449550 0.989890
3、v$sql_cs_histogram
用於決定一個查詢是否允許自適應遊標共享，以直方圖形式儲存
- SQL> select CHILD_NUMBER,BUCKET_ID,COUNT from v$sql_cs_histogram where SQL_ID='7qcp6urqh7d2j'
- 2 order by 1;
- CHILD_NUMBER BUCKET_ID COUNT
- ------------ ---------- ----------
- 0 1 1
- 0 0 1
- 0 2 0
- 1 1 0
- 1 0 1
- 1 2 0
- 2 1 0
- 2 0 1
- 2 2 0
- 3 1 0
- 3 0 1
- 3 2 0
三、總結
1、自適應遊標共享在SQL語句首次執行時(使用繫結變數)，進行窺探，並記錄窺探結果，如果後續有相同的的SQL語句執行，則對窺探結果進行比較以判斷是否需要生成新的執行計劃。此即為繫結變數是否敏感。
2、繫結變數的可知性用於判斷當前的遊標是否為可擴充套件性遊標共享，當不可知時，則遊標被廢棄。
3、自適應遊標共享的實質是在Oracle 10g以前的基礎上實現了多次繫結變數窺探，增加了獲取最佳執行計劃選擇的機率。
4、儘管使用自適應遊標共享特性，但並不能保證每次執行SQL語句一定按最佳計劃執行，本次演示中SQL語句的第二次執行並非最佳執行計劃。
5、自適應遊標共享也不能保證兩次執行相同SQL語句一定按相同的執行計劃執行，因為自適應遊標共享會不斷嘗試peeking。
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2140584/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle自適應共享遊標——Adaptive Cursor Sharing（上）OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（下）OracleAPT
- Oracle自適應共享遊標Oracle
- 揭秘自適應遊標共享技術(Adaptive Cursor Sharing)APT
- 揭祕自適應遊標共享技術(Adaptive Cursor Sharing)APT
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- Oracle遊標共享，父遊標和子游標的概念Oracle
- 11G Adaptive Cursor Sharing(ACS)自適應遊標測試APT
- 自適應遊標共享(ACS)與sql計劃管理(SPM)的相互影響SQL
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- 從共享遊標shared cursor角度看forall批量繫結
- Oracle 11.1 自適應遊標Oracle
- SQL 遊標cursorSQL
- oracle cursor遊標迴圈比較遊標元素是否相同Oracle
- 【CURSOR】Oracle 遊標 （cursor）知識梳理Oracle
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 關於sql語句的遊標共享問題SQL
- Oracle使用cursor for隱式遊標Oracle
- oracle cursor遊標獲取首末元素Oracle
- Oracle遊標Oracle
- Oracle 遊標Oracle
- PL/SQL 04 遊標 cursorSQL
- zt_Oracle9i,10g,11g 使用繫結變數的區別及與cursor_sharing的關係_自適應遊標共享Oracle變數
- PLSQL 呼叫 返回 遊標(Cursor)PROCEDURESQL
- SQL 遊標cursor的運用SQL
- Oracle遊標示例Oracle
- Oracle遊標大全Oracle
- PL/SQL-遊標和遊標變數的使用SQL變數
- flask-sqlalchemy中使用cursor遊標FlaskSQL
- Oracle動態遊標Oracle
- MySQL遊標和異常處理MySql
- SQL 遊標SQL
- Oracle Package返回遊標 和 java呼叫OraclePackageJava
- 11g自適應遊標與 SQL 計劃管理SQL
- oracle遊標使用全解Oracle
- Oracle 遊標使用全解Oracle
- [轉]oracle 遊標使用大全Oracle
- Oracle 遊標使用大全(轉)Oracle