bind peeking
不使用绑定变量时的执行计划
可以将shared pool中的执行计划失效,使当object_id<9000时生成并应用新的执行计划,可以通过执行一些跟相关对象(bind表)有关联的无关紧要的ddl语句来实现,比如:grant to 或 comment on。
也可以将参数_optim_peek_user_binds设置为false,将bind peeking失效,但是由于bug:4567767的存在,bind peeking还是有可能发生的。
2.可以将shared pool中的执行计划生效,以生成并应用新的执行计划,这可以通过执行一些跟相关对象(bind表)有关联的无关紧要的ddl语句来实现,比如:grant to 或 comment on。
3.为了减少带绑定变量的sql的解析时间,oracle 9i才引入的绑定变量窥探的功能,这个功能可以通过一个隐含参数”_optim_peek_user_binds”打开或关闭(注意bug:4567767的存在,提醒),注意_optim_peek_user_binds设置为false时,对执行计划的影响。
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 :x :=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 :x :=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 0 --sql_id是一样的。
-------------------------------------
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 |
------------------------------------------------------------------------------
--依然选择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')
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 --依然是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行。
可以将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 :x :=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行。
总结:
2.可以将shared pool中的执行计划生效,以生成并应用新的执行计划,这可以通过执行一些跟相关对象(bind表)有关联的无关紧要的ddl语句来实现,比如:grant to 或 comment on。
3.为了减少带绑定变量的sql的解析时间,oracle 9i才引入的绑定变量窥探的功能,这个功能可以通过一个隐含参数”_optim_peek_user_binds”打开或关闭(注意bug:4567767的存在,提醒),注意_optim_peek_user_binds设置为false时,对执行计划的影响。