bind peeking
oracle 在处理带有绑定变量的sql时，只会在硬解析的时候“窥探”一下sql中绑定变量的值，然后会根据窥探到的值来决定整个sql的执行计划。当以后该sql再次执行的时候，即使绑定变量的值发生了变化，oracle 也会使用该sql第一次执行（硬解析）时缓存在shared pool 中的执行计划。这可能会带来问题。
SQL> select * from v$version where rownum <2; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
不使用绑定变量时的执行计划
SQL> create table bind as select object_id,owner,object_name from dba_objects where rownum<9000;
表已创建。
SQL> set linesize 120
SQL> select count(*),count(object_id),count(distinct object_id),min(object_id),max(object_id) from bind;
COUNT(*) COUNT(OBJECT_ID) COUNT(DISTINCTOBJECT_ID) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- ---------------- ------------------------ -------------- --------------
8999 8999 8999 2 9275
SQL> create index idx_bind on bind(object_id);
索引已创建。
SQL> begin --收集statistics，不收集直方图信息
2 dbms_stats.gather_table_stats(
3 ownname=>'LAUGHING',
4 tabname=>'BIND',
5 cascade=>true,
6 estimate_percent=>100,
7 method_opt=>'for all columns size 1');
8
9 end;
10
11 /
PL/SQL 过程已成功完成。
--不使用绑定变量时的执行计划。
SQL> select count(*) from bind where object_id<9000;
COUNT(*)
----------
8745
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 16kc1bmrfy6f5, child number 0
-------------------------------------
select count(*) from bind where object_id<9000
Plan hash value: 1191041164
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
|* 2 | INDEX FAST FULL SCAN| IDX_BIND | 8732 | 34928 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<9000)
已选择19行。
SQL> select count(*) from bind where object_id<4;
COUNT(*)
----------
2
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 4jzk757t2x53g, child number 0
-------------------------------------
select count(*) from bind where object_id<4
Plan hash value: 2397605893
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_BIND | 2 | 8 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<4)
已选择19行。
以上不管是object_id<4还是object<9000均产生了最优的执行计划。
使用绑定变量的执行计划
--使用绑定变量的执行计划 SQL> var x number; SQL> exec:=4; PL/SQL 过程已成功完成。 SQL> select count(*) from bind where object_id<:x; COUNT(*) ---------- 2 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- SQL_ID 9zbk0412n0tgs, child number 0 ------------------------------------- select count(*) from bind where object_id<:x Plan hash value: 2397605893 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 4 | | | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- |* 2 | INDEX RANGE SCAN| IDX_BIND | 2 | 8 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / BIND@SEL$1 Outline Data ------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX(@"SEL$1" "BIND"@"SEL$1" ("BIND"."OBJECT_ID")) END_OUTLINE_DATA */ PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :X (NUMBER): 4 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<:X) Column Projection Information (identified by operation id): PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 已选择48行。 SQL> exec
可以将shared pool中的执行计划失效，使当object_id<9000时生成并应用新的执行计划，可以通过执行一些跟相关对象（bind表）有关联的无关紧要的ddl语句来实现，比如：grant to 或 comment on。
SQL> grant select on bind to scott;
授权成功。
SQL> select count(*) from bind where object_id<:x;
COUNT(*)
----------
8745
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 9zbk0412n0tgs, child number 0
-------------------------------------
select count(*) from bind where object_id<:x
Plan hash value: 1191041164
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
|* 2 | INDEX FAST FULL SCAN| IDX_BIND | 8732 | 34928 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------
--选择了INDEX FAST FULL SCAN
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / BIND@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "BIND"@"SEL$1" ("BIND"."OBJECT_ID"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 9000 --应用了新的绑定变量值。
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<:X)
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
已选择48行。
也可以将参数_optim_peek_user_binds设置为false,将bind peeking失效，但是由于bug:4567767的存在，bind peeking还是有可能发生的。
SQL> alter session set "_optim_peek_user_binds"=false;
会话已更改。
SQL> select count(*) from bind where object_id<:x;
COUNT(*)
----------
2
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID 9zbk0412n0tgs, child number 1
-------------------------------------
select count(*) from bind where object_id<:x
Plan hash value: 2397605893
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_BIND | 450 | 1800 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / BIND@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "BIND"@"SEL$1" ("BIND"."OBJECT_ID"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
已选择44行。
SQL> exec :=9000
PL/SQL 过程已成功完成。
SQL> select count(*) from bind where object_id<:x;
COUNT(*)
----------
8745
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 9zbk0412n0tgs, child number 1
-------------------------------------
select count(*) from bind where object_id<:x
Plan hash value: 2397605893
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_BIND | 450 | 1800 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
--关闭_optim_peek_user_binds之后，object_id<9000时，依然选择INDEX RANGE SCAN ，这显然不是最优的执行计划。
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / BIND@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "BIND"@"SEL$1" ("BIND"."OBJECT_ID"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
已选择44行。
总结：
1.当使用绑定变量时，由于bind peeking，oracle执行计划可能不是最优的。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。
2.可以将shared pool中的执行计划生效，以生成并应用新的执行计划，这可以通过执行一些跟相关对象（bind表）有关联的无关紧要的ddl语句来实现，比如：grant to 或 comment on。
3.为了减少带绑定变量的sql的解析时间，oracle 9i才引入的绑定变量窥探的功能，这个功能可以通过一个隐含参数”_optim_peek_user_binds”打开或关闭（注意bug:4567767的存在，提醒），注意_optim_peek_user_binds设置为false时，对执行计划的影响。