【DB笔试面试586】在Oracle中，什么是自适应游标共享（1）？
【DB笔试面试586】在Oracle中，什么是自适应游标共享（1）？
♣
题目部分
在Oracle中，什么是自适应游标共享？
♣
答案部分
绑定变量窥探的副作用就在于，使用了绑定变量的目标SQL只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合于当前的情形。在Oracle 10g及其后续的版本中，Oracle会自动收集直方图统计信息，这意味着与之前的版本相比，在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况，也就是说绑定变量窥探的副作用将会更加明显。当Oracle执行绑定变量窥探操作时绑定变量所对应的输入值是否具有代表性就至关重要了（这里“代表性”是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同），因为这会直接决定此目标SQL在硬解析时所选择的执行计划，进而决定后续以软解析/软软解析重复执行时所沿用的执行计划。
为了解决上述绑定变量窥探所带来的问题，Oracle在l1g中引入了自适应游标共享（Adaptive Cursor Sharing，ACS）。自适应游标共享可以让使用了绑定变量的目标SQL在启用了绑定变量窥探的前提条件下，不再像之前那样必须刻板地只沿用之前硬解析时所产生的解析树和执行计划，而是让目标SQL在其可能的多个执行计划之间“自适应”地做出选择。自适应游标共享的核心就在其能“自适应”地选择执行计划，从而就在一定程度上避免了绑定变量窥探的副作用。Oracle只需要在它认为目标SQL的执行计划可能发生变化时，触发该SQL再做一次硬解析就好了。因为一旦触发了硬解析这个动作，Oracle就会将目标SQL重新再解析一遍，其中就包括对该SQL再做一次绑定变量窥探。显然，再做一次绑定变量窥探后所对应的执行计划就是当前情形下CBO认为的最优执行计划，这个执行计划很可能和该SQL硬解析时所产生的执行计划不一样了。也就是说，一个简单的适时触发目标SQL再做一次硬解析的动作就在一定程度上缓解了绑定变量窥探所带来的副作用。
那么Oracle会在什么时候触发上述硬解析动作？或者说这里的“适时触发”的具体含义是什么？总的来说，Oracle会根据执行目标SQL时所对应的runtime统计信息（比如所耗费的逻辑读和CPU时间，对应结果集的行数等）的变化，以及当前传入的绑定变量输入值所在的谓词条件的可选择率，来综合判断是否需要触发目标SQL的硬解析动作。
先介绍Oracle数据库中与自适应游标共享相关的一些基本概念。
Bind Sensitive | Bind Aware | |
|---|---|---|
步骤 | 1 | 2 |
简介 | 自适应游标共享要做的第一件事就是所谓的扩展游标共享（Extended Cursor Sharing），而扩展游标共享做的主要事情就是将目标SQL所对应的Child Cursor标记为Bind Sensitive。Bind Sensitive是指Oracle觉得某个含绑定变量的目标SQL的执行计划可能会随着所传入的绑定变量输入值的变化而变化。对于标记为Bind Sensitive的Child Cursor，Oracle会把执行该SQL时所对应的runtime统计信息额外地存储在该SQL所对应的Child Cursor中。 | 自适应游标共享要做的第二件事情就是将目标SQL所对应的Child Cursor标记为Bind Aware。Bind Aware是指Oracle已经确定某个含绑定变量的目标SQL的执行计划会随着所传入的绑定变量输入值的变化而变化。 |
标记的条件 | 当满足如下3个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Sensitive：①启用了绑定变量窥探。②该SQL使用了绑定变量（不管是该SQL自带的绑定变量，还是开启常规游标共享后系统产生的绑定变量）。③该SQL使用的是不安全的谓词条件（例如范围查询，目标列上有直方图统计信息的等值查询等）。 | 当满足如下两个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Aware：①该SQL所对应的Child Cursor在之前已经被标记为Bind Sensitive。②该SQL在接下来连续两次执行时，所对应的runtime统计信息与该SQL之前硬解析时所对应的runtime统计信息均存在较大差异。 |
禁用 | 将隐含参数“_OPTIMIZER_EXTENDED_CURSOR_SHARING”和“_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL”的值均设为NONE。需要注意的是，若绑定变量的个数超过14，则该SQL对应的子游标将不会被标记为Bind Sensitive。 | 将隐含参数“_OPTIMIZER_ADAPTIVE_CURSOR_SHARING”的值设为FALSE。 |
字段 | V$SQL.IS_BIND_SENSITIVE | V$SQL.IS_BIND_AWARE |
自适应游标共享要做的第一件事就是所谓的扩展游标共享（Extended Cursor Sharing），而扩展游标共享做的主要事情就是将目标SQL所对应的Child Cursor标记为Bind Sensitive。Bind Sensitive是指Oracle觉得某个含绑定变量的目标SQL的执行计划可能会随着所传入的绑定变量输入值的变化而变化。
当满足如下三个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Sensitive：
l 启用了绑定变量窥探。
l 该SQL使用了绑定变量（不管是该SQL自带的绑定变量，还是开启常规游标共享后系统产生的绑定变量）。
l 该SQL使用的是不安全的谓词条件（例如范围查询，目标列上有直方图统计信息的等值查询等）。
自适应游标共享要做的第二件事情就是将目标SQL所对应的Child Cursor标记为Bind Aware。Bind Aware是指Oracle已经确定某个含绑定变量的目标SQL的执行计划会随着所传入的绑定变量输入值的变化而变化。
当满足如下两个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Aware：
l 该SQL所对应的Child Cursor在之前已经被标记为Bind Sensitive。
l 该SQL在接下来连续两次执行时，所对应的runtime统计信息与该SQL之前硬解析时所对应的runtime统计信息均存在较大差异。
对于自适应游标共享而言，V$SQL中的列IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE分别用来表示Child Cursor是否是Bind Sensitive、Bind Aware和共享的。这里“共享”的含义是指存储在该Child Cursor中的解析树和执行计划是否能被重用，一个非共享的Child Cursor中存储的执行计划和解析树是不能被重用的，并且该Child Cursor也会在第一时间从Shared Pool中被清理出去。
与自适应游标共享相关的有两个重要视图分别是V$SQL_CS_STATISTICS和V$SQL_CS_SELECTIVITY：
l V$SQL_CS_STATISTICS用于显示指定Child Cursor中存储的runtime统计信息。
l V$SQL_CS_SELECTIVITY用于显示指定的、己经被标记为Bind Aware的Child Cursor中存储的含绑定变量的谓词条件所对应的可选择率的范围。当一个被标记为Bind Aware的Child Cursor所对应的目标SQL再次被执行时，Oracle就会比较当前传入的绑定变量值所在的谓词条件的可选择率，以及该SQL之前硬解析时同名谓词条件在V$SQL_CS_SELECTIVITY中对应的可选择率的范围，并以此来决定此时的执行是用硬解析还是软解析／软软解析。
在介绍完上述基本概念后，现在就可以介绍自适应游标共享的整体执行流程了。Oracle数据库中自适应游标共享的整体执行流程为如下所示：
（1）当目标SQL第一次被执行时，Oracle会用硬解析，同时Oracle会根据一系列条件（如该SQL有没有使用绑定变量，参数CURSOR_SHARING的值是多少，绑定变量所在的列是否有直方图，该SQL的WHERE条件是等值查询还是范围查询等）来判断是否将该SQL所对应的Child Cursor标记为Bind Sensitive。对于标记为Bind Sensitive的Child Cursor，Oracle会把执行该SQL时所对应的runtime统计信息额外地存储在该SQL所对应的Child Cursor中。
（2）当目标SQL第二次被执行时，Oracle会用软解析，并且会重用该SQL第一次执行时所产生的Child Cursor中存储的解析树和执行计划。
（3）当目标SQL第三次被执行时，如果该SQL所对应的Child Cursor己经被标记成了Bind Sensitive，同时Oracle在第二次和第三次执行该SQL时所记录的runtime统计信息和该SQL第一次硬解析时所记录的runtime统计信息均存在较大差异，那么该SQL在第三次被执行时就会使用硬解析，Oracle此时会产生一个新的Child Cursor（这个新的Child Cursor会挂在原Parent Cursor下），并且Oracle会把这个新的Child Cursor标记为Bind Aware。
（4）对于标记为Bind Aware的Child Cursor所对应的目标SQL，当该SQL再次被执行时，Oracle就会根据当前传入的绑定变量值所对应的谓词条件的可选择率，来决定此时是用硬解析还是用软解析/软软解析。这里的判断原则是，如果当前传入的绑定变量值所在的谓词条件的可选择率处于该SQL之前硬解析时同名谓词条件在V$SQL_CS_STATISTICS中记录的可选择率的范围之内，那么此时Oracle就会使用软解析／软软解析，并重用相关Child Cursor中存储的解析树和执行计划，反之则是硬解析。如果是硬解析，且该次硬解析所产生的执行计划和原有Child Cursor中存储的执行计划相同，那么Oracle此时除了会新生成一个Child Cursor之外，还会把存储相同执行计划的原有Child Cursor标记为非共享（原有Child Cursor在V$SQL中对应记录的列IS_SHAREABLE的值也会从Y变为N)，在把原有Child cursor标记为非共享的同时，Oracle还会对新生成的Child Cursor执行一个Cursor合并的过程（这里Cursor合并的含义是指Oracle会合并存储相同执行计划的原有Child Cursor和新生成的Child Cursor）；如果是软解析/软软解析，那么Oracle会重用相关Child Cursor中存储的解析树和执行计划。
自适应游标共享虽然在一定程度上缓解了绑定变量窥探所带来的副作用，但它存在如下缺陷：
l 会额外地增加硬解析数量。
l 会额外地增加同一个父游标下子游标的数量，这会增加软解析/软软解析时查找匹配子游标的工作量。
l 为了存储这些额外增加地子游标，共享池（Shared Pool）在空间方面也会承受额外的压力。所以当从Oracle 10g升级到11g时，建议适当增加共享池的大小。
l 若绑定变量个数超过14个，则ACS失效。
如果因为开启ACS而导致系统产生了过多的子游标，进而导致共享池的空间紧张或者过多的Mutex等待，那么可以通过如下任意一种方式来禁用ACS：
l 将隐含参数“_OPTIMIZER_EXTENDED_CURSOR_SHARING”和“_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL”的值均设为NONE，这样就相当于关闭了可扩展游标共享。一旦可扩展游标共享被禁，所有的Child Cursor都将不能再被标记为Bind Sensitive，那么自然就不能被标记为Bind Aware，也就是说此时自适应游标共享就相当于被禁掉了。
l 将隐含参数“_OPTIMIZER_ADAPTIVE_CURSOR_SHARING”的值设为FALSE。一旦此隐含参数的值被设为FALSE，则所有的Child Cursor都将不能再被标记为Bind Aware（即使它们己经被标记成了Bind Sensitive），也就是说此时自适应游标共享就被直接禁掉了。
这里需要注意的是，自适应游标共享在Oracle 11g中有一个硬限制——只有当目标SQL中的绑定变量（不管这个绑定变量是该SQL自带的还是开启常规游标共享后系统产生的）的个数不超过14个时，自适应游标共享才会生效；一旦超过14，则该SQL对应的Child Cursor就永远不会被标记为Bind Sensitive，那么自适应游标共享就失效了。
下面给出一个自适应游标的示例：
数据库版本为11.2.0.3，准备基础数据：
CREATE TABLE T_ACS_20170611_LHR AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX IDX_ACS_OBJID_LHR ON T_ACS_20170611_LHR(OBJECT_ID);
SELECT COUNT(1) FROM T_ACS_20170611_LHR;
UPDATE T_ACS_20170611_LHR T SET T.OBJECT_TYPE='TABLE' WHERE ROWNUM<=60001; --更新数据，让OBJECT_TYPE变得不均衡
UPDATE T_ACS_20170611_LHR T SET T.OBJECT_TYPE='CLUSTER' WHERE ROWNUM<=2;
COMMIT;
LHR@orclasm > SELECT T.OBJECT_TYPE,COUNT(*) COUNTS FROM T_ACS_20170611_LHR T GROUP BY T.OBJECT_TYPE ORDER BY 2 DESC;
OBJECT_TYPE COUNTS
------------------- ----------
TABLE 61818
SYNONYM 3718
INDEX 3082
JAVA CLASS 2381
VIEW 1231
TYPE 973
INDEX PARTITION 738
TRIGGER 592
INDEX SUBPARTITION 585
PACKAGE 560
PACKAGE BODY 545
LOB 541
TABLE PARTITION 315
TABLE SUBPARTITION 223
FUNCTION 159
LOB SUBPARTITION 150
LOB PARTITION 121
SEQUENCE 109
TYPE BODY 96
PROCEDURE 55
JAVA RESOURCE 31
OPERATOR 25
LIBRARY 20
QUEUE 19
RULE SET 16
DIRECTORY 14
DATABASE LINK 12
XML SCHEMA 7
DIMENSION 5
PROGRAM 5
EVALUATION CONTEXT 5
JAVA DATA 4
MATERIALIZED VIEW 4
RULE 4
JOB 2
CLUSTER 2
JAVA SOURCE 2
CONTEXT 2
INDEXTYPE 2
UNDEFINED 1
--执行WHERE条件中带OBJECT_TYPE列的SQL语句，以便让基表COL_USAGE$可以记录下该列，便于后续自动收集该列上的统计信息：
LHR@orclasm > SELECT OO.NAME OWNER,
2 O.NAME TABLE_NAME,
3 C.NAME COLUMN_NAME,
4 U.EQUALITY_PREDS,
5 U.EQUIJOIN_PREDS,
6 U.NONEQUIJOIN_PREDS,
7 U.RANGE_PREDS,
8 U.LIKE_PREDS,
9 U.NULL_PREDS,
10 U.TIMESTAMP
11 FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C
12 WHERE O.OBJ# = U.OBJ#
13 AND OO.USER# = O.OWNER#
14 AND C.OBJ# = U.OBJ#
15 AND C.COL# = U.INTCOL#
16 AND O.NAME='T_ACS_20170611_LHR'
17 ;
no rows selected
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE='TABLE';
COUNT(*)
----------
61818
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE='CLUSTER';
COUNT(*)
----------
2
LHR@orclasm > SELECT OO.NAME OWNER,
2 O.NAME TABLE_NAME,
3 C.NAME COLUMN_NAME,
4 U.EQUALITY_PREDS,
5 U.EQUIJOIN_PREDS,
6 U.NONEQUIJOIN_PREDS,
7 U.RANGE_PREDS,
8 U.LIKE_PREDS,
9 U.NULL_PREDS,
10 U.TIMESTAMP
11 FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C
12 WHERE O.OBJ# = U.OBJ#
13 AND OO.USER# = O.OWNER#
14 AND C.OBJ# = U.OBJ#
15 AND C.COL# = U.INTCOL#
16 AND O.NAME='T_ACS_20170611_LHR'
17 ;
no rows selected
LHR@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT OO.NAME OWNER,
2 O.NAME TABLE_NAME,
3 C.NAME COLUMN_NAME,
4 U.EQUALITY_PREDS,
5 U.EQUIJOIN_PREDS,
6 U.NONEQUIJOIN_PREDS,
7 U.RANGE_PREDS,
8 U.LIKE_PREDS,
9 U.NULL_PREDS,
10 U.TIMESTAMP
11 FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C
12 WHERE O.OBJ# = U.OBJ#
13 AND OO.USER# = O.OWNER#
14 AND C.OBJ# = U.OBJ#
15 AND C.COL# = U.INTCOL#
16 AND O.NAME='T_ACS_20170611_LHR'
17 ;
OWNER TABLE_NAME COLUMN_NAME EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
------------------------------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
LHR T_ACS_20170611_LHR OBJECT_TYPE 1 0 0 0 0 0 2017-06-11 08:34:34
LHR@orclasm >
LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ACS_20170611_LHR',ESTIMATE_PERCENT => 100,CASCADE => TRUE,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',NO_INVALIDATE => FALSE);
PL/SQL procedure successfully completed.
LHR@orclasm >
LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_BUCKETS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME='T_ACS_20170611_LHR' AND D.COLUMN_NAME='OBJECT_TYPE';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
OBJECT_TYPE 40 40 FREQUENCY
--在保持隐含参数“_OPTIM_PEEK_USER_BINDS”和参数CURSOR_SHARING的值均为其默认值的条件下，定义绑定变量接着实验：
LHR@orclasm > ALTER SYSTEM FLUSH SHARED_POOL; --生产库慎用
System altered.
LHR@orclasm > conn lhr/lhr
Connected.
LHR@orclasm > VAR X VARCHAR2(30);
LHR@orclasm > EXEC :X :='CLUSTER';
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
COUNT(*)
----------
2
LHR@orclasm > col SQL_TEXT format a88
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';
SQL_TEXT SQL_ID VERSION_COUNT
---------------------------------------------------------------------------------------- ------------- -------------
SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 1
LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';
SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------- ------------ ---------- ----------- - - -
bt8tk3f1tnwcf 0 1 54 Y N Y
目标SQL的谓词条件为“OBJECT_TYPE=:X”，这个谓词条件是一个含绑定变量的等值查询条件，而且目标列OBJECT_TYPE上有FREQUENCY类型的直方图统计信息，所以该谓词条件是一个不安全的谓词条件。同时此SQL在执行时又启用了绑定变量窥探，这意味着Oracle会把该SQL对应的Child Cursor标记为Bind Sensitive。
从上述查询结果可以看到，目标SQL对应的IS_BIND_SENSITIVE的值为Y，IS_BIND_AWARE的值为N，IS_SHAREABLE的值为Y，这说明该SQL对应的Child Cursor确实己经被Oracle标记为Bind Sensitive；同时，该Child Cursor也是可共享的，但它现在还不是Bind Aware的。另外，上述Child Cursor所对应的runtime统计信息BUFFER_GETS（即逻辑读）的值为54，这是正常的，因为当绑定变量的值为“CLUSTER”时，目标SQL所对应结果集的Cardinality的值仅为2。
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID bt8tk3f1tnwcf, child number 0
-------------------------------------
SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
Plan hash value: 3002671579
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2 | 14 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_TYPE"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
49 rows selected.
LHR@orclasm >
从上述显示内容可以看出，Oracle此时选择的执行计划是走对索引IDX_ACS_OBJID_LHR的索引范围扫描。注意到“Peeked Binds”部分的内容为“1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'”，这说明Oracle在硬解析目标SQL的过程中确实使用了绑定变量窥探，且做“窥探”这个动作时看到的绑定变量攴的输入值为“CLUSTER”。
现在将X的值修改为"TABLE”：
LHR@orclasm > EXEC :X :='TABLE';
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
COUNT(*)
----------
61818
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 1 2
LHR@orclasm >
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL_ID bt8tk3f1tnwcf, child number 0
-------------------------------------
SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
Plan hash value: 3002671579
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2 | 14 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_TYPE"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
49 rows selected.
LHR@orclasm >
LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';
SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------- ------------ ---------- ----------- - - -
bt8tk3f1tnwcf 0 2 309 Y N Y
可以看到此时VERSION_COUNT的值为1，列EXECUTIONS的值为2，说明Oracle在第二次执行目标SQL时用的是软解析；从目标SQL的执行计划现在依然走的是对索引IDX_ACS_OBJID_LHR的索引范围扫描，并且“Peeked Binds”部分的内容依然为“1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'”。显然，这里Oracle沿用了之前硬解析时对应的解析树和执行计划，即绑定变量窥探起作用了。
从查询结果也可以看到，目标SQL对应的IS_BIND_SENSITIVE的值为Y，IS_BIND_AWARE的值为N，IS_SHAREABLE的值为Y，与之前比这些值均没有发生变化。但我们注意到，上述Child Cursor所对应的runtime统计信息BUFFER GETS的值从之前的54猛增到现在的309，己经有了较大变化，不过这也是正常的。因为当绑定变量攴的值为“TABLE”时，目标SQL所对应结果集的cardinality的值是61818，而不再是之前的2了。
之前在介绍Bind Aware时己经提过：目标SQL所对应的Child Cursor被标记为Bind Aware的必要条件，就是该SQL在接下来连续两次执行时所对应的runtime统计信息和该SQL硬解析时所对应的runtime统计信息均存在较大差异。虽然这里逻辑读BUFFER GETS的值确实发生了较大变化，但上述SQL在的值为“TABLE”的情况下只执行了一次，所以还不满足被标记为Bind Aware的前提条件，IS_BIND_AWARE的值当然就是N了。
V$SQL_CS_SELECTIVITY用于显示指定的、己经被标记为Bind Aware的Child Cursor中存储的含绑定变量的谓词条件所对应的可选择率的范围。上述Child Cursor还没有被标记为Bind Aware，所以现在以目标SQL对应的SQL_ID去查询视图V$SQL_CS_SELECTIVITY时是看不到对应的记录的：
LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf';
no rows selected
--在绑定变量X的值为TABLE的情况下再次执行目标SQL：
LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
COUNT(*)
----------
61818
LHR@orclasm >
现在该SQL对应的Child Cursor己经被标记为Bind Sensitive了，且该SQL接下来连续两次执行时所对应的runtime统计信息，以及该SQL之前硬解析时所对应的runtime统计信息均存在较大差异，那么此时Oracle在执行该SQL时就会使用硬解析，即Oracle此时会产生一个新的Child Cursor（这个新的Child Cursor会挂在原Parent Cursor下），并且Oracle会把这个新的Child Cursor标记为Bind Aware。
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 2 3
LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';
SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------- ------------ ---------- ----------- - - -
bt8tk3f1tnwcf 0 2 309 Y N N
bt8tk3f1tnwcf 1 1 522 Y Y Y
可以看到，上述SQL对应的列VERSION_COUNT的值从之前的1变为现在的2，列EXECUTIONS的值为3，说明Oracle在第三次执行该SQL时确实用的是硬解析。V$SQL多了一个CHILD NUMBER为1的新Child Cursor，且该Child Cursor对应的IS_BIND_SENSITIVE、IS_BIND_AWARE和IS_SHAREABLE的值均为Y，这说明该SQL在本次硬解析时新生成的Child cursor确实己经被Oracle标记为Bind Aware，同时，该Child Cursor也是可共享的。
目标SQL现在的执行计划为如下所示：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',1,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID bt8tk3f1tnwcf, child number 1
-------------------------------------
SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
Plan hash value: 4256744017
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 89 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_ACS_OBJID_LHR | 61818 | 422K| 89 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=852): 'TABLE'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OBJECT_TYPE"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
49 rows selected.
从上述显示内容可以看出，Oracle此时选择的执行计划是走对索引IDX_ACS_OBJID_LHR的索引快速全扫描。注意到“Peeked Binds”部分的内容为“1 - :X (VARCHAR2(30), CSID=852): 'TABLE'”，说明Oracle在硬解析上述SQL的过程中确实再次使用了绑定变量窥探，且做“窥探”这个动作时看到的绑定变量的输入值为"TABLE”。
CHILD_NUMBER为1的Child Cursor己经被标记成了Bind Aware，所以现在以目标SQL对应的SQL_ID去查询视图V$SQL_CS_SELECTIVITY时就能看到对应的记录了：
LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 =X 0 0.711697 0.869852
LHR@orclasm > SELECT * FROM V$SQL_CS_STATISTICS D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY D.CHILD_NUMBER;
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
00000000AA2108A8 2207936910 bt8tk3f1tnwcf 0 821942781 Y 1 3 54 0
00000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 3197905255 Y 1 61819 522 0
从上述显示内容可以看出，原SQL中的谓词条件“=:x”对应的可选择率的范围为[0.711697，0.869852]，即可选择率范围的下限为0.711697，上限为0.869852。
这个可选择率的范围是如何算出来的呢？Oracle首先计算做硬解析时（做了绑定变量窥探后）上述谓词条件的可选择率（这里将计算出来的可选择率记为S)，然后将S上下浮动10％就得到了上述可选择率的范围，即可选择率范围的计算公式为[0.9*S，1.1*S]。
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。