专栏首页小麦苗的DB宝专栏【DB笔试面试586】在Oracle中,什么是自适应游标共享(3)?

【DB笔试面试586】在Oracle中,什么是自适应游标共享(3)?

现在将的值修改为“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

LHR@orclasm > SELECT D.SID,D.SQL_ID,D.CURSOR_TYPE,D.SQL_TEXT FROM V$OPEN_CURSOR D WHERE D.SQL_ID='bt8tk3f1tnwcf';

       SID SQL_ID        CURSOR_TYPE                                                      SQL_TEXT
---------- ------------- ---------------------------------------------------------------- ----------------------------------------------------------------------------------------
        33 bt8tk3f1tnwcf DICTIONARY LOOKUP CURSOR CACHED                                  SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYP

LHR@orclasm > SELECT D.SQL_ID,D.CHILD_NUMBER,D.BIND_EQUIV_FAILURE FROM v$sql_shared_cursor d WHERE d.SQL_ID='bt8tk3f1tnwcf';

SQL_ID        CHILD_NUMBER B
------------- ------------ -
bt8tk3f1tnwcf            0 N
bt8tk3f1tnwcf            1 Y
bt8tk3f1tnwcf            2 Y
bt8tk3f1tnwcf            3 Y
bt8tk3f1tnwcf            4 Y
bt8tk3f1tnwcf            5 Y

LHR@orclasm > 

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试586】在Oracle中,什么是自适应游标共享(2)?

    另外,在目标列有Frequency类型直方图的前提条件下,如果对目标列施加等值查询条件,且该查询条件的输入值等于该列的某个实际值时,则该谓词条件的可选择率的计算...

    小麦苗DBA宝典
  • 【DB笔试面试670】在Oracle中,什么是SQL实时监控?

    在Oracle 11g中,V$SESSION视图增加了一些新的字段,这其中包括SQL_EXEC_START和SQL_EXEC_ID,这两个字段实际上代表了Ora...

    小麦苗DBA宝典
  • 【DB笔试面试464】动态SQL是什么?

    在PL/SQL开发过程中,使用SQL或PL/SQL可以实现大部分的需求,但是,在某些特殊的情况下,在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的...

    小麦苗DBA宝典
  • SQLPlus 常用命令

    3->LIST [m][*] [n](简写L)显示缓冲区的所有内容。* 当前行,m 第m行,n 第n行,m n 同时出现,m到n行

    Leshami
  • 插入"&"特殊字符的几种思考

    一位铁杆朋友,今天问了个问题,写了一个Python程序,从文件读取数据,其中可能包含“&”这种特殊字符,为了让其能插入Oracle,需要做什么处理?

    bisal
  • View Merge 在安全控制上的变化,是 BUG 还是增强 ?

    View Merge 是 12C 引入的新特性,也是一种优化手段。当查询中引用了 View 或 inline view 时,优化器可以将主查询中的查询条件并入视...

    数据和云01
  • SQL反模式学习笔记21 SQL注入

      通常所说的“SQL动态查询”是指将程序中的变量和基本SQL语句拼接成一个完整的查询语句。

    张传宁老师
  • 如何让你的 SQL 执行的飞起?

    我知道准是上午人甲产品经理又来了一个脏活。话说 SQL 程序员本身是个光荣的职业,顷刻间百万数据、百亿金额从指间流过,心都不带咯噔的。在心如止水的 SQL 编码...

    Lenis
  • 如何找到抛出ORA-00933错误的SQL

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    bisal
  • SQL之美 - Oracle 子查询优化系列精讲

    题记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。 本系列...

    数据和云

扫码关注云+社区

领取腾讯云代金券