Oracle_自适应游标共享的整体执行流程

Oracle 自适应游标共享的整体执行流程，如下:

1、当目标SQL第一次被执行时，Oracle会用硬解析，同时Oracle会根据一系列条件(如:SQL有没有使用绑定变量，参数cursor_shared的值是什么，绑定变量所在

列是否有直方图，该sql的where条件是等值查询还是范围查询等),来判断是否将该SQL所对应的child cursor标记为bind sensitive，对于标记为bind sensitive的child

cursor ,oracle 会把执行该sql时所对应的runtime统计信息额外地存储在该sql所对应的child cursor中。

    查询视图:select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE ,IS_BIND_AWARE,IS_SHAREABLE from v$sql where sql_id='bkpynbfzkjxnu'

2、当目标SQL 第二次被执行时，Oracle会用软解析，并且会重用该SQL第一次执行时所产生的child cursor中存储的解析树和执行计划。

3、当目标SQL第三次被执行时，如果该sql所对应的child cursor已经被标记成了 bind sensitive,同时oracle在第二次和第三次执行该sql时所记录的runtime统计信息和该SQL第一次硬解析时差异较大，这是会使用硬解析。

4、对于标记为bind aware的child cursor所对应的目标SQL,当该SQL再次被执行时，oracle会自动判断，是采用硬解析还是软解析。

下面，我们对自适应游标共享的示例进行测试。

1、创建测试表

       create table sdxj.t4 as select * from dba_objects;
2、创建索引
      create index sdxj.idx_t4 on sdxj.t4 (object_type);
3、修改数据使其数据不均匀分布
      update sdxj.t4 set object_type='TABLE' WHERE ROWNUM<60000;
      update sdxj.t4 set object_type='CLUSTER' WHERE ROWNUM<2;
4、查询结果
      SQL> SELECT COUNT(*) FROM SDXJ.T4;
     COUNT(*)
     ----------
     72446
     SQL> SELECT COUNT(*) FROM sdxj.t4 WHERE  object_type='CLUSTER';
     COUNT(*)
     -------
         1
     SQL> SELECT COUNT(*) FROM sdxj.t4 WHERE  object_type='TABLE';
    COUNT(*)
    ----------
     61525

5、收集表T4直方图信息

     exec dbms_stats.gather_table_stats(ownname => 'SDXJ',tabname => 'T4',estimate_percent => 100,CASCADE => TRUE,no_invalidate  => FALSE,method_opt => 'for all columns size auto',degree => 16);

6、查看收集结果

    sql>select LOW_VALUE,HIGH_VALUE,LAST_ANALYZED,NUM_BUCKETS, HISTOGRAM from DBA_TAB_COL_STATISTICS where owner='SDXJ' AND TABLE_NAME='T4'  AND COLUMN_NAME='OBJECT_TYPE';

LOW_VALUE                                                        HIGH_VALUE                                               LAST_ANALYZED  NUM_BUCKETS HISTOGRAM
---------------------------------------------------------------- ---------------------------------------------------------------- -------------- ----------- --
434C5553544552                                                   584D4C20534348454D41                                     16-8月 -15              30 FREQUENCY

7、进行测试

   在测试之前保持隐含参数 _OPTIM_PEEK_USER_BINDS 和 CURSOR_SHARING为默认值。

   sql> alter system flush shared_pool   ---生产环境禁止操作。

   

定义变量x 并进行赋值

sql> var x varchar2(30);

sql> exec :x :='CLUSTER';

sql>select count(*) from sdxj.t4 where object_type=:x;

sql>SELECT  SQL_TEXT,SQL_ID,VERSION_COUNT ,executions,ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM SDXJ.T4 WHERE OBJECT%';

SQL_TEXT                                           SQL_ID                                             VERSION_COUNT EXECUTIONS ADDRESS  HASH_VALUE
-------------------------------------------------- -------------------------------------------------- ------------- ---------- -------- ----------
SELECT COUNT(*) FROM SDXJ.T4 WHERE OBJECT_TYPE=:X  b7yj2ja74bygg                                          1          1 B4E68BB4 2386950639

SQL> SELECT CHILD_NUMBER,EXECUTIONS,BUFFER_GETS,IS_BIND_SENSITIVE ,IS_BIND_AWARE,IS_SHAREABLE,PLAN_HASH_VALUE  FROM V$SQL WHERE SQL_ID='b7yj2ja74bygg';

目标sql执行计划

Plan hash value: 1219564997
---------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|
|   1 |  SORT AGGREGATE   |        |     1 |     7 |            |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| IDX_T4 |     1 |     7 |     3   (0)| 00:00:01
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T4@SEL$1

Outline Data
------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T4"@"SEL$1" ("T4"."OBJECT_TYPE"))
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (VARCHAR2(30), CSID=873): 'CLUSTER'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"=:X)

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

已选择49行。

现在将x 的值修改为 “Table"

SQL> exec :x :='TABLE'

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM SDXJ.T4 WHERE OBJECT_TYPE=:x;

  COUNT(*)
----------
     61525

Plan hash value: 2030376245

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    83 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T4 | 61525 |   420K|    83   (2)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T4@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T4"@"SEL$1" ("T4"."OBJECT_TYPE"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------
   1 - :X (VARCHAR2(30), CSID=873): 'TABLE'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"=:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

已选择49行。

sql>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='dg8wsunwkvq57';

selecttivity =bucketsize /num_rows

解释:

a、上述计算公式适用于启用了绑定变量窥探，目标列有Frequency 类型的直方图，对目标施行等值查询条件

并且查询条件的输入值等于该列的某个实际值时的计算。

b、num_rows 表示目标列所在表的记录数

c、bucketsize 表示目标列的某个实际值所对应的记录数。

--实际计算结果：

SELECT ROUND((61525/72446)*0.9,6),ROUND((61525/72446)*1.1,6)  FROM DUAL;

###禁用自适用游标共享。

1、将隐含参数_optimizer_extended_cursor_sharing 和_optimizer_extended_cursor_sharing——的值改为NONE

2、将隐含参数 _optimizer_adaptive_cursor_sharing 的值设为false, 一旦隐含参数被设置为false 则所以的child cursor都将不能被标记为bind aware.