【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15759字，纯文字阅读大概需要23分钟。
内容图文
现在将的值修改为“JAVA CLASS”，然后再次执行目标SQL：
1LHR@orclasm > EXEC :X :='CLUSTER'; 2 3PL/SQL procedure successfully completed. 4 5LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X; 6 7 COUNT(*) 8---------- 9 2 10 11LHR@orclasm > 12LHR@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=%'; 13 14SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS 15---------------------------------------------------------------------------------------- ------------- ------------- ---------- 16SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 6 7 17 18LHR@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'; 19 20SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE 21------------- ------------ ---------- ----------- - - - --------------- 22bt8tk3f1tnwcf 0 2 309 Y N N 3002671579 23bt8tk3f1tnwcf 1 1 522 Y Y Y 4256744017 24bt8tk3f1tnwcf 2 1 16 Y Y N 3002671579 25bt8tk3f1tnwcf 3 1 79 Y Y N 3002671579 26bt8tk3f1tnwcf 4 1 74 Y Y N 3002671579 27bt8tk3f1tnwcf 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现在的执行计划为如下所示：
1LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',5,'advanced'));
2
3PLAN_TABLE_OUTPUT
4-----------------------------------------------------------------------------
5SQL_ID bt8tk3f1tnwcf, child number 5
6-------------------------------------
7SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
8
9Plan hash value: 3002671579
10
11---------------------------------------------------------------------------------------
12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
13---------------------------------------------------------------------------------------
14| 0 | SELECT STATEMENT | | | | 3 (100)| |
15| 1 | SORT AGGREGATE | | 1 | 7 | | |
16|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2 | 14 | 3 (0)| 00:00:01 |
17---------------------------------------------------------------------------------------
18
19Query Block Name / Object Alias (identified by operation id):
20-------------------------------------------------------------
21
22 1 - SEL$1
23 2 - SEL$1 / T@SEL$1
24
25Outline Data
26-------------
27
28 /*+
29 BEGIN_OUTLINE_DATA
30 IGNORE_OPTIM_EMBEDDED_HINTS
31 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
32 DB_VERSION('11.2.0.3')
33 ALL_ROWS
34 OUTLINE_LEAF(@"SEL$1")
35 INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
36 END_OUTLINE_DATA
37 */
38
39Peeked Binds (identified by position):
40--------------------------------------
41
42 1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'
43
44Predicate Information (identified by operation id):
45---------------------------------------------------
46
47 2 - access("T"."OBJECT_TYPE"=:X)
48
49Column Projection Information (identified by operation id):
50-----------------------------------------------------------
51
52 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，带入合并后的计算公式：
1LHR@orclasm > SELECT ROUND(0.9*(2/78174),6) low,ROUND(1.1*(2/78174),6) HIGH FROM DUAL; 2 3 LOW HIGH 4---------- ---------- 5 0.000023 0.000028 6 7LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY CHILD_NUMBER; 8 9ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH 10---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 1100000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 =X 0 0.711697 0.869852 1200000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 =X 0 0.035482 0.043367 1300000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 =X 0 0.035482 0.052317 1400000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 =X 0 0.027412 0.052317 1500000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 =X 0 0.000023 0.052317 16 17LHR@orclasm > SELECT * FROM V$SQL_CS_STATISTICS D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY D.CHILD_NUMBER; 18 19ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME 20---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 2100000000AA2108A8 2207936910 bt8tk3f1tnwcf 0 821942781 Y 1 3 54 0 2200000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 3197905255 Y 1 61819 522 0 2300000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 3279106319 Y 1 3083 16 0 2400000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 3683986157 Y 1 3719 79 0 2500000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 4071504174 Y 1 2382 74 0 2600000000AA2108A8 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：
1LHR@orclasm > EXEC :X :='VIEW'; 2 3PL/SQL procedure successfully completed. 4 5LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X; 6 7 COUNT(*) 8---------- 9 1231 10 11LHR@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=%'; 12 13SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS 14---------------------------------------------------------------------------------------- ------------- ------------- ---------- 15SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 6 8 16 17LHR@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'; 18 19SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I PLAN_HASH_VALUE 20------------- ------------ ---------- ----------- - - - --------------- 21bt8tk3f1tnwcf 0 2 309 Y N N 3002671579 22bt8tk3f1tnwcf 1 1 522 Y Y Y 4256744017 23bt8tk3f1tnwcf 2 1 16 Y Y N 3002671579 24bt8tk3f1tnwcf 3 1 79 Y Y N 3002671579 25bt8tk3f1tnwcf 4 1 74 Y Y N 3002671579 26bt8tk3f1tnwcf 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现在的执行计划为如下所示：
1LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',5,'advanced'));
2
3PLAN_TABLE_OUTPUT
4-----------------------------------------------------------------------------
5SQL_ID bt8tk3f1tnwcf, child number 5
6-------------------------------------
7SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
8
9Plan hash value: 3002671579
10
11---------------------------------------------------------------------------------------
12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
13---------------------------------------------------------------------------------------
14| 0 | SELECT STATEMENT | | | | 3 (100)| |
15| 1 | SORT AGGREGATE | | 1 | 7 | | |
16|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2 | 14 | 3 (0)| 00:00:01 |
17---------------------------------------------------------------------------------------
18
19Query Block Name / Object Alias (identified by operation id):
20-------------------------------------------------------------
21
22 1 - SEL$1
23 2 - SEL$1 / T@SEL$1
24
25Outline Data
26-------------
27
28 /*+
29 BEGIN_OUTLINE_DATA
30 IGNORE_OPTIM_EMBEDDED_HINTS
31 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
32 DB_VERSION('11.2.0.3')
33 ALL_ROWS
34 OUTLINE_LEAF(@"SEL$1")
35 INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
36 END_OUTLINE_DATA
37 */
38
39Peeked Binds (identified by position):
40--------------------------------------
41
42 1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'
43
44Predicate Information (identified by operation id):
45---------------------------------------------------
46
47 2 - access("T"."OBJECT_TYPE"=:X)
48
49Column Projection Information (identified by operation id):
50-----------------------------------------------------------
51
52 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，带入合并后的计算公式：
1LHR@orclasm > SELECT ROUND(0.9*(1231/78174),6) low,ROUND(1.1*(1231/78174),6) HIGH FROM DUAL; 2 3 LOW HIGH 4---------- ---------- 5 0.014172 0.017322 6 7LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY CHILD_NUMBER; 8 9ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH 10---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 1100000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 =X 0 0.711697 0.869852 1200000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 =X 0 0.035482 0.043367 1300000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 =X 0 0.035482 0.052317 1400000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 =X 0 0.027412 0.052317 1500000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 =X 0 0.000023 0.052317 16 17LHR@orclasm > SELECT * FROM V$SQL_CS_STATISTICS D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY D.CHILD_NUMBER; 18 19ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME 20---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 2100000000AA2108A8 2207936910 bt8tk3f1tnwcf 0 821942781 Y 1 3 54 0 2200000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 3197905255 Y 1 61819 522 0 2300000000AA2108A8 2207936910 bt8tk3f1tnwcf 2 3279106319 Y 1 3083 16 0 2400000000AA2108A8 2207936910 bt8tk3f1tnwcf 3 3683986157 Y 1 3719 79 0 2500000000AA2108A8 2207936910 bt8tk3f1tnwcf 4 4071504174 Y 1 2382 74 0 2600000000AA2108A8 2207936910 bt8tk3f1tnwcf 5 821942781 Y 1 3 3 0 27 28LHR@orclasm > SELECT D.SID,D.SQL_ID,D.CURSOR_TYPE,D.SQL_TEXT FROM V$OPEN_CURSOR D WHERE D.SQL_ID='bt8tk3f1tnwcf'; 29 30 SID SQL_ID CURSOR_TYPE SQL_TEXT 31---------- ------------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------- 32 33 bt8tk3f1tnwcf DICTIONARY LOOKUP CURSOR CACHED SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYP 33 34LHR@orclasm > SELECT D.SQL_ID,D.CHILD_NUMBER,D.BIND_EQUIV_FAILURE FROM v$sql_shared_cursor d WHERE d.SQL_ID='bt8tk3f1tnwcf'; 35 36SQL_ID CHILD_NUMBER B 37------------- ------------ - 38bt8tk3f1tnwcf 0 N 39bt8tk3f1tnwcf 1 Y 40bt8tk3f1tnwcf 2 Y 41bt8tk3f1tnwcf 3 Y 42bt8tk3f1tnwcf 4 Y 43bt8tk3f1tnwcf 5 Y 44 45LHR@orclasm >
内容总结
以上是互联网集市为您收集整理的【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？全部内容，希望文章能够帮你解决【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。