【DB笔试面试586】在Oracle中，什么是自适应游标共享（4）？

• 2019 年 10 月 10 日
• 筆記

从上述计算结果可以看出，现在计算出的可选择率范围为[0.014172，0.017322]，在CHILD_NUMBER为5的原有Child Cursor对应的可选择率范围[0.000023，0.052317]之内，所以刚才Oracle在执行上述SQL时（即第8次执行目标SQL）用的就是软解析/软软解析，并且此时重用的就是CHILD_NUMBER为5的Child Cursor中存储的解析树和执行计划。

从上述对自适应游标共享的整个测试过程可以看出，自适应游标共享虽然在一定程度上缓解了绑定变量窥探所带来的副作用，但自适应游标共享并不是完美的，它可能存在如下缺陷：

l 可能导致一定数量的额外的硬解析（比如上述目标SQL总共执行了8次，但有6次都是硬解析）。

l 可能导致一定数量的额外的Child Cursor挂在同一个Parent Cursor下（比如上述目标SQL总共执行了8次，但产生了6个Child Cursor），这会增加软解析/软软解析时查找匹配Child Cursor的工作量。

l 为了存储这些额外的Child Cursor，Shared Pool在空间方面也会承受额外的压力（所以当从Oracle 10g升级到Oracle 11g时，Oracle会建议适当增加Shared Pool的大小）。

如果因为开启自适应游标共享而导致系统产生了过多的Child Cursor，进而导致Shared Pool的空间紧张或者过多的Mutex等待，那么可以通过如下任意一种方式来禁用自适应游标共享：

l 将隐含参数“_OPTIMIZER_EXTENDED_CURSOR_SHARING”和“_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL”的值均设为NONE，这样就相当于关闭了可扩展游标共享。一旦可扩展游标共享被禁，所有的Child Cursor都将不能再被标记为Bind Sensitive。而被标记为Bind Sensitive是Child Cursor被后续标记为Bind Aware的前提条件，一旦不能被标记为Bind Sensitive，则后续的Bind Aware就无从谈起，也就是说此时自适应游标共享就相当于被禁掉了。

l 将隐含参数“_OPTIMIZER_ADAPTIVE_CURSOR_SHARING”的值设为FALSE。一旦此隐含参数的值被设为FALSE，则所有的Child Cursor都将不能再被标记为Bind Aware（即使它们己经被标记成了Bind Sensitive），也就是说此时自适应游标共享就被直接禁掉了。

这里需要注意的是，自适应游标共享在Oracle 11g中有一个硬限制——只有当目标SQL中的绑定变量（不管这个绑定变量是该SQL自带的还是开启常规游标共享后系统产生的）的个数不超过14个时，自适应游标共享才会生效；一旦超过14，则该SQL对应的Child Cursor就永远不会被标记为Bind Sensitive，那么自适应游标共享就失效了。

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

这里还是以前面测试自适应游标共享时所用到的表T_ACS_20170611_LHRTI为例来说明。

把CURSOR_SHARING的值改为FORCE，然后在保持隐含参数“_OPTIM_PEEK_USER_BINDS”值为其默认值的情况下清空Shared pool（为了排除干扰）：

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

依据之前自适应游标共享的测试结果，可以推断出如果自适应游标共享对上述SQL生效的话，则上述SQL在被执行了三次的情况下应该会产生两个Child Cursor。

从如下查询结果可以看到，上述SQL在被Oracle用系统产生的绑定变量替换后对应记录的列VERSION_COUNT的值为2，列EXECUTIONS的值为3，这说明上述SQL在被执行了三次的情况下确实产生了两个Child Cursor，即当目标SQL中的绑定变量的数量不超过14时，自适应游标共享确实生效了：

LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';    SQL_TEXT                                                                                 SQL_ID        VERSION_COUNT EXECUTIONS  ---------------------------------------------------------------------------------------- ------------- ------------- ----------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:"SYS_B_00" AND T.OBJECT_T 65ggkpkp6n7mq             2          3  YPE=:"SYS_B_01" AND T.OBJECT_TYPE=:"SYS_B_02" AND T.OBJECT_TYPE=:"SYS_B_03" AND T.OBJECT  _TYPE=:"SYS_B_04" AND T.OBJECT_TYPE=:"SYS_B_05" AND T.OBJECT_TYPE=:"SYS_B_06" AND T.OBJE  CT_TYPE=:"SYS_B_07" AND T.OBJECT_TYPE=:"SYS_B_08" AND T.OBJECT_TYPE=:"SYS_B_09" AND T.OB  JECT_TYPE=:"SYS_B_10" AND T.OBJECT_TYPE=:"SYS_B_11" AND T.OBJECT_TYPE=:"SYS_B_12" AND T.  OBJECT_TYPE=:"SYS_B_13"    LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE,A.PLAN_HASH_VALUE FROM V$SQL A WHERE A.SQL_ID='65ggkpkp6n7mq';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE  ------------- ------------ ---------- ----------- - - - ---------------  65ggkpkp6n7mq            0          2         309 Y N N      2878087074  65ggkpkp6n7mq            1          1         522 Y Y Y      3865303624    LHR@orclasm >  

该目标SQL多执行几次后：

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

我们再来看看带15个绑定变量的情形：

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

依据之前常规游标共享和自适应游标共享的测试结果，可以推断出如果自适应游标共享对上述SQL不起作用的话，那么常规游标共享就会起作用，即上述SQL在被执行了三次的情况下应该只会产生一个Child Cursor。

从如下查询结果可以看到，上述SQL在被Oracle用系统产生的绑定变量替换后，对应记录的列VERSION_COUNT的值为1，列EXECUTIONS的值为3，这说明上述SQL在被执行了三次的情况下确实只产生了一个Child Cursor，即当目标SQL中的绑定变量的数量超过14时，自适应游标共享确实没有生效：

LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';    SQL_TEXT                                                                                 SQL_ID        VERSION_COUNT EXECUTIONS  ---------------------------------------------------------------------------------------- ------------- ------------- ----------  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:"SYS_B_00" AND T.OBJECT_T 6zmk2h81jnava             1          3  YPE=:"SYS_B_01" AND T.OBJECT_TYPE=:"SYS_B_02" AND T.OBJECT_TYPE=:"SYS_B_03" AND T.OBJECT  _TYPE=:"SYS_B_04" AND T.OBJECT_TYPE=:"SYS_B_05" AND T.OBJECT_TYPE=:"SYS_B_06" AND T.OBJE  CT_TYPE=:"SYS_B_07" AND T.OBJECT_TYPE=:"SYS_B_08" AND T.OBJECT_TYPE=:"SYS_B_09" AND T.OB  JECT_TYPE=:"SYS_B_10" AND T.OBJECT_TYPE=:"SYS_B_11" AND T.OBJECT_TYPE=:"SYS_B_12" AND T.  OBJECT_TYPE=:"SYS_B_13" AND T.OBJECT_TYPE=:"SYS_B_14"      LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE,A.PLAN_HASH_VALUE FROM V$SQL A WHERE A.SQL_ID='6zmk2h81jnava';    SQL_ID        CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE  ------------- ------------ ---------- ----------- - - - ---------------  6zmk2h81jnava            0          3         568 N N Y      2878087074  

至此，我们己经介绍完Oracle数据库中与自适应游标共享相关的全部内容。