ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 9550|回复: 9

[精华] 验证11g的adaptive cursor sharing

[复制链接]
论坛徽章:
10
2011新春纪念徽章
日期:2011-02-18 11:43:342015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09优秀写手
日期:2013-12-18 09:29:11三菱
日期:2013-08-30 20:37:412013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51暖羊羊
日期:2015-06-22 15:51:36
跳转到指定楼层
1#
发表于 2012-7-26 16:25 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
当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.
案例1  
开启变量窥测,收集直方图
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生成新的执行计划
论坛徽章:
10
2011新春纪念徽章
日期:2011-02-18 11:43:342015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09优秀写手
日期:2013-12-18 09:29:11三菱
日期:2013-08-30 20:37:412013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51暖羊羊
日期:2015-06-22 15:51:36
2#
 楼主| 发表于 2012-7-26 16:26 | 只看该作者
案例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=1时oracle依旧选择了索引扫描
案例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生成不同的子游标,由于缺乏正确的统计信息也可能导致沿用错误的执行计划,若不开启peeking则adaptive cursor sharing根本无从谈起。

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:022012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
3#
发表于 2012-7-30 10:18 | 只看该作者
很用心的实验,精华鼓励

使用道具 举报

回复
论坛徽章:
10
2011新春纪念徽章
日期:2011-02-18 11:43:342015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09优秀写手
日期:2013-12-18 09:29:11三菱
日期:2013-08-30 20:37:412013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51暖羊羊
日期:2015-06-22 15:51:36
4#
 楼主| 发表于 2012-7-30 11:29 | 只看该作者
anlinew 发表于 2012-7-30 10:18
很用心的实验,精华鼓励

十分受用  多谢鼓励  

使用道具 举报

回复
论坛徽章:
86
马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14
5#
发表于 2012-7-30 13:58 | 只看该作者
nice job.

一个小建议,最好用个表来做一下测试的总结。

使用道具 举报

回复
认证徽章
论坛徽章:
23
2013年新春福章
日期:2013-02-25 14:51:242013年新春福章
日期:2013-03-03 16:41:142013年新春福章
日期:2013-03-03 16:42:172013年新春福章
日期:2013-02-25 11:02:122013年新春福章
日期:2013-03-03 16:41:142013年新春福章
日期:2013-03-03 16:42:172014年世界杯参赛球队: 法国
日期:2014-06-13 11:24:502014年世界杯参赛球队: 波黑
日期:2014-06-13 15:38:252014年世界杯参赛球队: 伊朗
日期:2014-07-28 20:08:212014年世界杯参赛球队: 俄罗斯
日期:2014-07-17 17:21:42
6#
发表于 2012-8-3 18:56 | 只看该作者
学习了

使用道具 举报

回复
论坛徽章:
21
2014年世界杯参赛球队: 澳大利亚
日期:2014-06-03 17:51:05大众
日期:2013-09-11 15:40:41林肯
日期:2013-09-06 14:56:34福特
日期:2013-09-06 14:09:53保时捷
日期:2013-08-28 10:50:59凯迪拉克
日期:2013-09-09 15:40:01雪铁龙
日期:2013-09-09 20:15:04本田
日期:2013-09-18 20:39:04宝马
日期:2013-09-06 16:41:46一汽
日期:2013-09-16 23:57:01
7#
发表于 2013-9-11 15:16 | 只看该作者
很好的文章!~

使用道具 举报

回复
论坛徽章:
5
2013年新春福章
日期:2013-02-25 14:51:24兰博基尼
日期:2013-10-11 16:43:30优秀写手
日期:2013-12-18 09:29:132014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
8#
发表于 2013-9-24 10:09 | 只看该作者
曾经viadeaden写过,没看懂

使用道具 举报

回复
认证徽章
论坛徽章:
25
紫蛋头
日期:2013-01-11 17:47:18本田
日期:2013-08-27 13:29:542014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-03-20 16:13:24马上有房
日期:2014-03-20 16:14:11马上有钱
日期:2014-03-20 16:14:11马上有对象
日期:2014-03-20 16:14:11马上加薪
日期:2014-03-20 16:14:11秀才
日期:2016-03-24 09:20:52
9#
发表于 2016-4-16 17:24 | 只看该作者
myownstars 发表于 2012-7-26 16:26
案例2删除直方图SQL> exec dbms_stats.delete_column_stats('SYS','EMP','ID',col_stat_type =>'HISTOGRAM' ...

11G若要正确的使用adaptive cursor sharing功能,必须开启绑定变量窥视,且对应列需要收集直方图信息

我这2则都满足了,但是还是acs没起作用。

使用道具 举报

回复
认证徽章
论坛徽章:
25
紫蛋头
日期:2013-01-11 17:47:18本田
日期:2013-08-27 13:29:542014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-03-20 16:13:24马上有房
日期:2014-03-20 16:14:11马上有钱
日期:2014-03-20 16:14:11马上有对象
日期:2014-03-20 16:14:11马上加薪
日期:2014-03-20 16:14:11秀才
日期:2016-03-24 09:20:52
10#
发表于 2016-4-16 17:25 | 只看该作者
myownstars 发表于 2012-7-26 16:26
案例2删除直方图SQL> exec dbms_stats.delete_column_stats('SYS','EMP','ID',col_stat_type =>'HISTOGRAM' ...

一个时间的字段
用不同的变量测试,is_bind_sensitive都是N

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表