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] view plain copy print?
- 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] view plain copy print?
- 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] view plain copy print?
- 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] view plain copy print?
- 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] view plain copy print?
- 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] view plain copy print?
- 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群，学习最实用的数据库技术。
正文到此结束
- 本文标签： schema 博客 微信公众号 id 云 UI CTO 二维码 QQ群 http FAQ MQ 游标 CDN sql 空间 list 自适应 ask trigger IO mail 安全 时间 value 同步 DDL Job 图片 db cat 代码 key 总结 统计 文章 ORM tab 压力 SDN XML 开发 src Enterprise PL/SQL 测试 数据库 Menu Select 主键值 lib ACE Features bug update Oracle IDE 解析 参数 dist Word queue 专注 java tar 数据 HTML
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
