ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 验证11g的adaptive cursor sharing功能

验证11g的adaptive cursor sharing功能

原创 Linux操作系统 作者:myownstars 时间:2012-07-26 16:26:47 0 删除 编辑

oracle开启绑定变量窥视功能,会在sql硬解析时窥探变量的实际值以求生成更为精确的执行计划,但是若该列数据分布极度不均,则可能适得其反,11g引入了adaptive cursor sharing.

查询优化器会不断做出调整以便不同的绑定变量各自获取最优执行计划,该功能系统默认开启,无法手工禁止。

经此特性处理的sql需经历三个阶段: bind-sensitive -> bind-aware -> cursor merging

以下是11R2的原文解释,对于原文的理解可能有偏差,还是直接看英文文档比较好;

Bind-sensitive cursor

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior. of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:

·         The optimizer has peeked at the bind values to generate selectivity estimates.

·         A histogram exists on the column containing the bind value

Bind-aware cursor

When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:

·         Generates a new plan based on the new bind value.

·         Marks the original cursor generated for the statement

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate

Cursor merging

If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform. cursor merging. In this case, the database merges cursors to save space in the shared SQL area

 

做一组试验测试一下该功能

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

 

SQL> create table emp(id number(10), name varchar(500)) pctfree 40;

 

Table created.

 

SQL> begin

  2    for i in 1..1000 loop

  3      insert into emp values(1,lpad('x',300,'x'));

    insert into emp values(i,lpad('y',300,'x'));

  end loop;

  commit;

end;  4    5    6    7 

  8  /

 

PL/SQL procedure successfully completed

 

SQL> create index t_ind on emp(id);

 

Index created.

 

 

案例

开启变量窥测,收集直方图

SQL>  exec dbms_stats.gather_table_stats('SYS','EMP',estimate_percent => 100, cascade => true, method_opt =>'for all columns size skewonly');

 

PL/SQL procedure successfully completed.

 

SQL> select column_name,NUM_BUCKETS,DENSITY,NUM_NULLS from dba_tab_columns where TABLE_NAME='EMP';

 

COLUMN_NAME                                        NUM_BUCKETS    DENSITY  NUM_NULLS

-------------------------------------------------- ----------- ---------- ----------

NAME                                                         1     .00025          0

ID                                                         254      .0005          0

 

SQL> variable id number;

SQL> exec :id :=6;

 

PL/SQL procedure successfully completed.

 

SQL> select /*test*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7jrqjukk5g62z, child number 0

-------------------------------------

select /*test*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1 |   304 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

--查看视图,bind_sensitive=Y, bind_aware=N

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          1          25 Y          N          Y

 

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

------------ ------------------- - ---------- -------------- ----------- ----------

           0          1021760792 Y          1              3          25          0

--更改绑定变量值

SQL> exec :id := 1;

 

PL/SQL procedure successfully completed.

 

SQL> select /*test*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7jrqjukk5g62z, child number 0

-------------------------------------

select /*test*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1 |   304 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

--第一次并不会更改执行计划,但是v$sql.buffer_gets却发生了改变,由25增加到162

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          2         162 Y          N          Y

 

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

------------ ------------------- - ---------- -------------- ----------- ----------

           0          1021760792 Y          1              3          25          0

--第二次运行

SQL> select /*test*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7jrqjukk5g62z, child number 1—生成了一个新的子游标

-------------------------------------

select /*test*/ count(name) from emp where id = : id

 

Plan hash value: 1849991560

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |    39 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |   304 |            |          |

|*  2 |   TABLE ACCESS FULL| EMP  |   984 |   292K|    39   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("ID"=:ID)

 

 

19 rows selected.

--新生成的子游标选择全表扫描,且bind_aware=Y

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          2         162 Y          N          Y

           1          1         140 Y          Y          Y

 

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

------------ ------------------- - ---------- -------------- ----------- ----------

           1          2342552567 Y          1           1002         140          0

           0          1021760792 Y          1              3          25          0

 

--此时将id改为一个可以使用索引扫描的值

SQL> exec :id :=100;

 

PL/SQL procedure successfully completed.

 

SQL> select /*test*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7jrqjukk5g62z, child number 2

-------------------------------------

select /*test*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1 |   304 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

--生成一个新的子游标,且第一个子游标被设置为不可共享

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          2         162 Y          N          N

           1          1         140 Y          Y          Y

           2          1           3 Y          Y          Y

 

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

------------ ------------------- - ---------- -------------- ----------- ----------

           2           336594526 Y          1              3           3          0

           1          2342552567 Y          1           1002         140          0

           0          1021760792 Y          1              3          25          0

--第一个子游标已经被标识为不可共享,但若将id值重新改为6,还是可以重用

SQL> exec :id := 6;

 

PL/SQL procedure successfully completed.

 

SQL>  select /*test*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9jxcwk6y1fzpt, child number 0

-------------------------------------

 select /*test*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1 |   304 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

但是过了一会就无法再用了,有点奇怪

SQL>  exec :id := 6;

 

PL/SQL procedure successfully completed.

 

SQL> select /*test*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7jrqjukk5g62z, child number 2

-------------------------------------

select /*test*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     1 |   304 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          2         162 Y          N          N

           1          1         140 Y          Y          Y

           2          4          12 Y          Y          Y

--查看子游标无法共享的原因

SQL> select CHILD_NUMBER,BIND_EQUIV_FAILURE,LOAD_OPTIMIZER_STATS from v$sql_shared_cursor where sql_id='7jrqjukk5g62z';

 

CHILD_NUMBER B L

------------ - -

           0 N Y

           1 Y N

           2 Y N

-- BIND_EQUIV_FAILURE: The bind value's selectivity does not match that used to optimize the existing child cursor

-- LOAD_OPTIMIZER_STATS : A hard parse is forced in order to initialize extended cursor sharing

小结:当系统开启绑定变量窥测且收集了直方图时,oracle会对不同的绑定变量生成不同的子游标(但该变量必须运行至少两次),并依据selectivity生成新的执行计划

 

案例2

删除直方图

SQL> exec dbms_stats.delete_column_stats('SYS','EMP','ID',col_stat_type =>'HISTOGRAM');

 

PL/SQL procedure successfully completed.

 

SQL> select column_name,NUM_BUCKETS,DENSITY,NUM_NULLS from dba_tab_columns where TABLE_NAME='EMP';

 

COLUMN_NAME                                        NUM_BUCKETS    DENSITY  NUM_NULLS

-------------------------------------------------- ----------- ---------- ----------

NAME                                                         1     .00025          0

ID                                                           1       .001          0

 

 

SQL> exec :id := 600;

 

PL/SQL procedure successfully completed.

 

SQL> select /*justin*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL>  select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  6d84s04yqn3cp, child number 0

-------------------------------------

select /*justin*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL>  SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='6d84s04yqn3cp';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          1          22 Y          N          Y

 

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='6d84s04yqn3cp';

 

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

------------ ------------------- - ---------- -------------- ----------- ----------

           0          3287955162 Y          1              3          22          0

 

id改为1

SQL> exec :id :=1;

 

PL/SQL procedure successfully completed.

 

SQL> select /*justin*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  6d84s04yqn3cp, child number 0

-------------------------------------

select /*justin*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL> select /*justin*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  6d84s04yqn3cp, child number 1

-------------------------------------

select /*justin*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

--运行两次后生成一个新的子游标,但是仍旧选择索引扫描

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='6d84s04yqn3cp';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          2         159 Y          N          N

           1          1         137 Y          Y          Y

 

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='6d84s04yqn3cp';

 

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME

------------ ------------------- - ---------- -------------- ----------- ----------

           1          2342552567 Y          1           2003         137          0

           0          3287955162 Y          1              3          22          0

小结:删除直方图,游标依旧为bind_sensitive,但是即便生成了不同的子游标,由于缺乏足够的统计信息,当id=1oracle依旧选择了索引扫描

 

案例3

没有直方图,也不开启绑定变量窥视

SQL> alter system set "_optim_peek_user_binds"=false;

 

System altered.

 

SQL> select /*thirid*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  01k335m3nbvty, child number 0

-------------------------------------

select /*thirid*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL> exec :id :=2;

 

PL/SQL procedure successfully completed.

 

SQL> select /*thirid*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  68jwfamnx5gty, child number 0

-------------------------------------

select /*thirid*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

--可以看到只生成一个子游标,且bind_sensitive=N

SQL>  SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='01k335m3nbvty';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

           0          1         137 N          N          Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='01k335m3nbvty';

 

no rows selected

小结:没有绑定变量窥测时,oracle不会生成不同的子游标

案例4

只有直方图,没有窥测

SQL> alter system set "_optim_peek_user_binds"=false;

 

System altered.

 

SQL>  select /*fourth*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

          1

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  ax6hs790n3ru2, child number 0

-------------------------------------

 select /*fourth*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL> exec :id :=1;

 

PL/SQL procedure successfully completed.

 

SQL> select /*fourth*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  5ru3vdjyva40h, child number 0

-------------------------------------

select /*fourth*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL> select /*fourth*/ count(name) from emp where id = : id;

 

COUNT(NAME)

-----------

       1001

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  5ru3vdjyva40h, child number 0

-------------------------------------

select /*fourth*/ count(name) from emp where id = : id

 

Plan hash value: 1800857609

 

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |   304 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP   |     2 |   608 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |     2 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID"=:ID)

 

 

20 rows selected.

 

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='5ru3vdjyva40h';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B

------------ ---------- ----------- - - -

           0          2         274 N N Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='5ru3vdjyva40h';

 

no rows selected

小结:没有绑定变量窥测时,oracle不会生成不同的子游标

 

总结:

11G若要正确的使用adaptive cursor sharing功能,必须开启绑定变量窥视,且对应列需要收集直方图信息,两者缺其一都会误导oracle做出错误的选择;

若只开启peeking,则即便使用了adaptive cursor sharing生成不同的子游标,由于缺乏正确的统计信息也可能导致沿用错误的执行计划,若不开启peekingadaptive cursor sharing根本无从谈起。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-736982/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3041098