当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生成新的执行计划
1034
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
