Oracle绑定变量窥视功能的使用与测试
绑定变量窥视功能：
从ORACLE9I引入，是否启用由隐含参数_optim_peek_user_binds控制，默认是TRUE,开启。
当绑定变量窥视启用以后，每当ORACLE以硬解析的方式解析使用绑定变量的目标SQL时，ORACLE会实际窥视PEEKING一下对应绑定变量的具体输入值，并以此为标准，确定可选择率selectivity和结果集的行数cardinality进而确定执行计划。
–这个窥视动作只有在硬解析时才有。(只需要对表做DDL操作–推荐添加注释的COMMENT语句，再次执行时会硬解析)
–另一种方式是删除shared pool 库缓存中的shared cursor。在10.2.0.4版本及以上可以使用，但是在10.2.0.4中使用则需要先设置5614566 EVENT才能生效。
绑定变量窥视功能推出为解决什么样的业务场景：
随着具体输入值不同，目标SQL的WHERE条件的可选择率selectivity和结果集的行数cardinality可能随之发生变化，而可选择率selectivity和结果集的行数cardinality的值会影响CBO对于相关执行步骤成本值的估算，从而影响CBO对目标SQL执行计划的选择，目标SQL的执行计划可能变化。
对使用绑定变量的SQL，ORACLE使用以下两种方法来决定执行计划：
1.使用绑定变量窥视
2.如果不使用绑定变量窥视，对于可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率5%。
绑定变量窥视相关的查询语句：
SQL> select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID ADDRESS HASH_VALUE
——————————————————————————– ————- ——– ———-
select count(*) from t1 where object_id between 
and :y 9dhu3xk2zu531 3967E8D0 2247955553
SQL> exec sys.dbms_shared_pool.purge(‘3967E8D0,2247955553′,’c’);
PL/SQL procedure successfully completed.
SQL> select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
no rows selected
–关闭绑定变量窥视的方法是 alter session set “_optim_peek_user_binds”=false;–可以动态调整
NAME VALUE DESCRIPTION
—————————————- —————————— —————————————-
_optim_peek_user_binds FALSE enable peeking of user binds
SQL> alter system set “_optim_peek_user_binds”=false;
System altered.
NAME VALUE DESCRIPTION
—————————————- —————————— —————————————-
_optim_peek_user_binds FALSE enable peeking of user binds
SQL> alter system set “_optim_peek_user_binds”=true;
System altered.
NAME VALUE DESCRIPTION
—————————————- —————————— —————————————-
_optim_peek_user_binds TRUE enable peeking of user binds
————————————————
优点：
避免使用默认的可选择率，有更大可能得到正确的执行计划。
缺点：
执行计划被固定，此执行计划取决于硬解析时的输入值，可能不是最做优。(11G自适应游标共享后有所缓解)。同时如果再次发生硬解析，执行计划可以变化，如变差则可能影响系统性能。
如下绑定变量窥视测试例子：
1.首先创建测试环境
在WHERE条件上使用两个绑定变量：where object_id between 999 and 1000;
测试出不使用绑定变量时–999 and 1000、1 and 80000的不同查询条件的执行计划是不同的。
2.测试绑定变量窥视开启时，不同绑定变量值在硬解析时会生成新执行计划
3.测试绑定变量窥视关闭时，不同绑定变量值在硬解析时不会生成新执行计划
#######################################
create table t1 as select * from dba_objects;
create index idx_t1 on t1(object_id);
select count(*) from t1;
COUNT(*)
———-
86756
SQL> select count(distinct(object_id)) from t1;
COUNT(DISTINCT(OBJECT_ID))
————————–
86756
exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>’BYS’,tabname =>’t1′,estimate_percent=>100,method_opt=>’for all columns size 1′,cascade=>TRUE,no_invalidate=>false);
SQL> select count(*) from t1 where object_id between 999 and 1000;
COUNT(*)
———-
2
SQL> select count(*) from t1 where object_id between 999 and 60000;
COUNT(*)
———-
58664
SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT
—————————————————————————————————- ————- ————-
select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1
select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1
查看执行计划：
col plan_table_output for a100
set long 900
set pagesize 100
select * from table(dbms_xplan.display_cursor(‘5gu397922cuqd’,0,’advanced’));
PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 5gu397922cuqd, child number 0
————————————-
select count(*) from t1 where object_id between 999 and 1000
Plan hash value: 1970818898
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |
—————————————————————————-
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / T1@SEL$1
Outline Data
————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID”>=999 AND “OBJECT_ID”<=1000)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
QL> select count(*) from t1 where object_id between 999 and 60000;
COUNT(*)
———-
58664
SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID b8xxw70vja3tn, child number 0
————————————-
select count(*) from t1 where object_id between 999 and 60000
Plan hash value: 2101382132
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | | | 54 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 57682 | 281K| 54 (2)| 00:00:01 |
——————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / T1@SEL$1
Outline Data
————-
Predicate Information (identified by operation id):
—————————————————
2 – filter((“OBJECT_ID”<=60000 AND “OBJECT_ID”>=999))
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
############################################################################
接下来使用绑定变量查看：
SQL> var x number;
SQL> var y number;
SQL> exec :=999
PL/SQL procedure successfully completed.
SQL> exec :y :=1000
SQL> conn bys/bys
Connected.
SQL> select count(*) from t1 where object_id between and :y;
COUNT(*)
———-
2
col sql_text for a80
set linesize 150
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between and :y 9dhu3xk2zu531 1 1
SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
———————————————————————————————————
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between and :y
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
————-
Peeked Binds (identified by position):
————————————–
1 – :X (NUMBER): 999
2 – :Y (NUMBER): 1000
Predicate Information (identified by operation id):
—————————————————
2 – filter(:Y>=:X)
3 – access(“OBJECT_ID”>=:X AND “OBJECT_ID”<=:Y)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
52 rows selected.
SQL> exec :y :=60000
PL/SQL procedure successfully completed.
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between and :y 9dhu3xk2zu531 1 1
SQL> select count(*) from t1 where object_id between and :y;
COUNT(*)
———-
58664
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between and :y 9dhu3xk2zu531 1 2
SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
————————————————————————————
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between and :y
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
————-
Peeked Binds (identified by position):
————————————–
1 – :X (NUMBER): 999
2 – :Y (NUMBER): 1000
Predicate Information (identified by operation id):
—————————————————
2 – filter(:Y>=:X)
3 – access(“OBJECT_ID”>=:X AND “OBJECT_ID”<=:Y)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
52 rows selected.
#############################3
对表做DDL操作，重新执行语句，会做绑定变量窥视。
SQL> comment on table t1 is ‘test1’;
Comment created.
SQL> select count(*) from t1 where object_id between and :y;
COUNT(*)
———-
58664
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between and :y 9dhu3xk2zu531 1 1
SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
———————————————————————————————————–
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between and :y
Plan hash value: 1410530761
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | | | 54 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX FAST FULL SCAN| IDX_T1 | 57682 | 281K| 54 (2)| 00:00:01 |
———————————————————————————
############################################
关闭绑定变量窥视后验证：
不会生成INDEX FAST FULL SCAN较好的执行计划。
SQL> show parameter peek
NAME TYPE VALUE
———————————— ———– ——————————
_optim_peek_user_binds boolean FALSE
SQL> comment on table t1 is ‘test1’;
Comment created.
SQL> var x number;
SQL> var y number;
SQL> exec :=1
PL/SQL procedure successfully completed.
SQL> exec :y :=80000
PL/SQL procedure successfully completed.
SQL> col plan_table_output for a160
SQL> set long 9000
SQL> set linesize 200
SQL> set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————————————————————————-
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between and :y
Plan hash value: 3858015043
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 25 | 125 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.5’)
OPT_PARAM(‘_optim_peek_user_binds’ ‘false’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX(@”SEL$1” “T1″@”SEL$1” (“T1″.”OBJECT_ID”))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
2 – filter(:X<=:Y)
3 – access(“OBJECT_ID”>=:X AND “OBJECT_ID”<=:Y)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
46 rows selected.