Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables. However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values
rajesh@10GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.89
rajesh@10GR2>
rajesh@10GR2> create table t
2 as
3 select 1 as id,
4 a.*
5 from all_objects a;
Table created.
Elapsed: 00:00:04.81
rajesh@10GR2>
rajesh@10GR2> insert into t
2 select 2 as id,
3 a.*
4 from all_objects a
5 where rownum = 1;
1 row created.
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(id) nologging;
Index created.
Elapsed: 00:00:01.21
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.57
variable x number;
exec :x := 2;
SELECT /* ACS */ *
FROM
T LOOK_FOR_ME WHERE ID = :X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.13 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.14 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=45 us)
1 INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=31 us)(object id 145050)
This is the Optimal plan, since we have one record for id = 2 out of 56250 record in Table ' T ', so doing an INDEX RANGE SCAN which is valid. How ever for the second query, Now the optimizer peeks at the value of the user-defined bind variable ( id = 1 )and re-uses the same plan again. which is far better to use FULL TABLE SCAN rather than INDEX RANGE SCAN.
exec :x := 1;
SELECT /* ACS */ *
FROM
T LOOK_FOR_ME WHERE ID = :X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 376 0.06 0.24 0 1657 0 56250
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 378 0.06 0.24 0 1657 0 56250
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 100
Rows Row Source Operation
------- ---------------------------------------------------
56250 TABLE ACCESS BY INDEX ROWID T (cr=1657 pr=0 pw=0 time=168785 us)
56250 INDEX RANGE SCAN T_IND (cr=486 pr=0 pw=0 time=56272 us)(object id 145050)
Now repeating the same scenario on Oracle 11gR2 database involves Adaptive Cursor Sharing into picture.
rajesh@11GR2> drop table t purge;
Table dropped.
Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> create table t
2 as
3 select 1 as id,
4 a.*
5 from all_objects a;
Table created.
Elapsed: 00:00:06.34
rajesh@11GR2>
rajesh@11GR2> insert into t
2 select 2 as id,
3 a.*
4 from all_objects a
5 where rownum = 1;
1 row created.
Elapsed: 00:00:00.09
rajesh@11GR2>
rajesh@11GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t(id) nologging;
Index created.
Elapsed: 00:00:00.50
rajesh@11GR2>
rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.90
rajesh@11GR2>
variable x number;
exec :x := 2;
SELECT /* ACS */ *
FROM
T LOOK_FOR_ME WHERE ID = :X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=2 size=100 card=1)
1 INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74786)
rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 0
EXECUTIONS---------- 1
BUFFER_GETS--------- 3
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- N
IS_SHAREABLE-------- Y
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware ( IS_BIND_AWARE = 'Y') , the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
Since now the cursor available in share pool is not bind aware ( IS_BIND_AWARE = 'N' ), Consequently, the optimizer uses the same index range scan for the value id = 1 as for the value id = 2.
exec :x := 1;
SELECT /* ACS */ *
FROM
T LOOK_FOR_ME WHERE ID = :X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 478 0.12 0.13 0 2139 0 71490
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 480 0.12 0.13 0 2139 0 71490
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
71490 TABLE ACCESS BY INDEX ROWID T (cr=2139 pr=0 pw=0 time=122607 us cost=2 size=100 card=1)
71490 INDEX RANGE SCAN T_IND (cr=618 pr=0 pw=0 time=42074 us cost=1 size=0 card=1)(object id 74786)
rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 0
EXECUTIONS---------- 2
BUFFER_GETS--------- 2142
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- N
IS_SHAREABLE-------- Y
Now execute the query using the ID = 1 a second time. The database compares statistics for previous executions and marks the cursor as bind-aware. In this case, the optimizer decides that a new plan is warranted, so it performs a hard parse of the statement and generates a new plan.
exec :x := 1;
SELECT /* ACS */ *
FROM
T LOOK_FOR_ME WHERE ID = :X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 478 0.15 0.73 0 1545 0 71490
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 480 0.15 0.73 0 1545 0 71490
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
71490 TABLE ACCESS FULL T (cr=1545 pr=0 pw=0 time=48043 us cost=315 size=7149000 card=71490)
rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 0
EXECUTIONS---------- 2
BUFFER_GETS--------- 2142
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- N
IS_SHAREABLE-------- Y
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 1
EXECUTIONS---------- 1
BUFFER_GETS--------- 1545
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- Y
IS_SHAREABLE-------- Y
After you execute the query twice with value ID = 1, you execute it again using the more selective value ID = 2. Because of adaptive cursor sharing, the optimizer "adapts" the cursor and chooses an index range scan rather than a full table scan for this value.
exec :x := 2;
SELECT /* ACS */ *
FROM
T LOOK_FOR_ME WHERE ID = :X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=2 size=100 card=1)
1 INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74786)
rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 0
EXECUTIONS---------- 2
BUFFER_GETS--------- 2142
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- N
IS_SHAREABLE-------- N
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 1
EXECUTIONS---------- 1
BUFFER_GETS--------- 1545
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- Y
IS_SHAREABLE-------- Y
SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
SQL_ID-------------- avh2ay05hnmdv
CHILD_NUMBER-------- 2
EXECUTIONS---------- 1
BUFFER_GETS--------- 3
IS_BIND_SENSITIVE--- Y
IS_BIND_AWARE------- Y
IS_SHAREABLE-------- Y
Database is now using adaptive cursor sharing, the database no longer uses the original cursor (child 0), which is not bind-aware.
(Also please be sure that Adaptive cursor sharing is available only from 11.1.0.7.0, but not before that. So if you try this in 11.1.0.6.0 you will never achieve).
Excellent willing analytical eyesight pertaining to details and may foresee issues just before they occur.
ReplyDeleteMy web site; 情趣用品