Force a query to use bind-aware cursor sharing
Starting with 11.1.0.7 the BIND_AWARE hint can be used to
force bind aware cursor sharing from the first execution, the hint only works
if the below criteria were met
- The query must have bind variables
- The binds must appear in where clause predicates
- The column in those predicates must have the proper statistics to allow the plan to actually change when the query is executed with different bind values
rajesh@ORA11G> create table t
2 as
3 select *
4 from all_objects ,
5 (select level from dual
6 connect by level <=20 ) ;
Table created.
rajesh@ORA11G> create index t_idx on t(object_id);
Index created.
rajesh@ORA11G>
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> variable x number
rajesh@ORA11G> exec :x := 1000000;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select /*+ bind_aware */ sum(data_object_id)
from t
2 where object_id > :x;
SUM(DATA_OBJECT_ID)
-------------------
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor
2
(format=>'+peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 1d8f9mhrxwx36, child number 0
-------------------------------------
select /*+ bind_aware */ sum(data_object_id) from t where
object_id > :x
Plan hash value: 1789076273
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 4 (100)|
| 1 | SORT AGGREGATE | |
1 | 7 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
1 | 7 | 4
(0)| 00:00:01
|* 3 | INDEX RANGE
SCAN | T_IDX | 1 |
| 3 (0)| 00:00:01
-------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER):
1000000
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("OBJECT_ID">:X)
25 rows selected.
rajesh@ORA11G> select child_number,executions,buffer_gets,
2 is_bind_sensitive, is_bind_aware,
is_shareable
3 from v$sql
4 where sql_id ='1d8f9mhrxwx36';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
0
1 3 Y Y Y
rajesh@ORA11G> exec :x := 1;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select /*+ bind_aware */ sum(data_object_id)
from t
2 where object_id > :x;
SUM(DATA_OBJECT_ID)
-------------------
7695803380
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor(format=>'+peeked_binds')
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 1d8f9mhrxwx36, child number 1
-------------------------------------
select /*+ bind_aware */ sum(data_object_id) from t where
object_id > :x
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 6861 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 7 | |
|
|* 2 | TABLE ACCESS FULL|
T | 1690K|
11M| 6861 (1)| 00:01:23 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("OBJECT_ID">:X)
24 rows selected.
rajesh@ORA11G> select child_number,executions,buffer_gets,
2 is_bind_sensitive, is_bind_aware,
is_shareable
3 from v$sql
4 where sql_id ='1d8f9mhrxwx36';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
0 1 3 Y Y Y
1 1 25057 Y Y Y
rajesh@ORA11G> exec :x := 30576;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select /*+ bind_aware */ sum(data_object_id)
from t
2 where object_id > :x;
SUM(DATA_OBJECT_ID)
-------------------
7389227920
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor(format=>'+peeked_binds')
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 1d8f9mhrxwx36, child number 2
-------------------------------------
select /*+ bind_aware */ sum(data_object_id) from t where
object_id > :x
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 6860 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 7 | | |
|* 2 | TABLE ACCESS FULL|
T | 1105K|
7560K| 6860 (1)| 00:01:23 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 30576
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("OBJECT_ID">:X)
24 rows selected.
rajesh@ORA11G> select child_number,executions,buffer_gets,
2 is_bind_sensitive, is_bind_aware,
is_shareable
3 from v$sql
4 where sql_id ='1d8f9mhrxwx36';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
0 1 3 Y Y Y
1 1 25057 Y Y N
2 1 25057 Y Y Y
rajesh@ORA11G> exec :x := 1078;
PL/SQL procedure successfully completed.
rajesh@ORA11G> select /*+ bind_aware */ sum(data_object_id)
from t
2 where object_id > :x;
SUM(DATA_OBJECT_ID)
-------------------
7684944080
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor(format=>'+peeked_binds')
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 1d8f9mhrxwx36, child number 2
-------------------------------------
select /*+ bind_aware */ sum(data_object_id) from t where
object_id > :x
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 6860 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 7 | | |
|* 2 | TABLE ACCESS FULL|
T | 1105K|
7560K| 6860 (1)| 00:01:23 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 30576
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("OBJECT_ID">:X)
24 rows selected.
rajesh@ORA11G> select child_number,executions,buffer_gets,
2 is_bind_sensitive, is_bind_aware, is_shareable
3 from v$sql
4 where sql_id ='1d8f9mhrxwx36';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
------------ ---------- ----------- - - -
0 1 3 Y Y Y
1 1 25057 Y Y N
2 2 50114 Y Y Y
rajesh@ORA11G>
Now that the cursor cache is populated with these three bind-aware
child cursors, they will behave just as if they were created due to adaptive
cursor sharing. For instance, if I run the query again with another very selective
bind value, the first child cursor will be used
No comments:
Post a Comment