Hi guys
Does the hint(/*+opt_param('_optim_peek_user_binds', 'false')*/) mean disable bind peaking?
Here are 2 sqls, withount any hints:
drop table t;
purge recyclebin;
create table t(id,owner,object_id) as select rownum,owner,object_id from all_objects where rownum<=1000;
alter table t add constraint t_pk primary key(id);
exec dbms_stats.gather_table_stats(ownname =>user,tabname =>'T' ,cascade =>true);
ALTER system flush shared_pool;
alter system flush buffer_cache;
set linesize 20000
set pagesize 40000
alter session set statistics_level=all;
sql1:
select sum(object_id) from t where id<10; --INDEX RANGE SCAN
sql2:
select sum(object_id) from t where id<900; --TABLE ACCESS FULL
But, if I put the use bind and hint to the sql like below:
select
/*+opt_param('_optim_peek_user_binds', 'false')*/
sum(object_id) from t where id<:v_id;
I firstly set v_id:=10 and run the sql(sql3) ,then set v_id:=900 and run the sql again(sql4), finding sql3 and sql4 are both INDEX RANGE SCAN.
To my understanding, sql4 should be TABLE ACCESS FULL in execution plan as I have disabled the bind peaking(different with the real execution plan INDEX RANGE SCAN). Looks like the hint "/*+opt_param('_optim_peek_user_binds', 'false')*/" make no difference.
Could you please explain how it happens?(My db is 10.2.0.1)
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table t;
purge recyclebin;
create table t(id,owner,object_id) as select rownum,owner,object_id from all_objects where rownum<=1000;
alter table t add constraint t_pk primary key(id);
exec dbms_stats.gather_table_stats(ownname =>user,tabname =>'T' ,cascade =>true);
ALTER system flush shared_pool;
alter system flush buffer_cache;
set linesize 20000
set pagesize 40000
alter session set statistics_level=all;
Table dropped.
SQL>
Recyclebin purged.
SQL>
Table created.
SQL>
Table altered.
SQL>
PL/SQL procedure successfully completed.
SQL>
System altered.
SQL>
System altered.
SQL> SQL> SQL>
Session altered.
SQL> SQL> SQL> SQL> SQL>
SQL> variable v_id number;
SQL> exec :v_id:=10
select
/*+opt_param('_optim_peek_user_binds', 'false')*/
sum(object_id) from t where id<:v_id;
select * from table(dbms_xplan.display_cursor());
PL/SQL procedure successfully completed.
SQL> 2 3
SUM(OBJECT_ID)
--------------
254
SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b6nmcrdabf2kg, child number 0
-------------------------------------
select /*+opt_param('_optim_peek_user_binds', 'false')*/ sum(object_id) from
t where id<:v_id
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<:V_ID)
21 rows selected.
SQL> SQL>
SQL>
SQL>
SQL> variable v_id number;
SQL> exec :v_id:=900
PL/SQL procedure successfully completed.
SQL> select
2 /*+opt_param('_optim_peek_user_binds', 'false')*/
3 sum(object_id) from t where id<:v_id;
SUM(OBJECT_ID)
--------------
445689
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b6nmcrdabf2kg, child number 0
-------------------------------------
select /*+opt_param('_optim_peek_user_binds', 'false')*/ sum(object_id) from
t where id<:v_id
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<:V_ID)
21 rows selected.
SQL>
SQL>
SQL>
Regards
Li