Hi John,
Here is the test case.
--Checking ACS related parameters (It is all default values)
SELECT a.KSPPINM "PARAMETER",
b.KSPPSTDF "IS_DEFAULT",
b.KSPPSTVL "SESSION",
c.KSPPSTVL "INSTANCE"
FROM X$KSPPI a, X$KSPPCV b, X$KSPPSV c
WHERE a.INDX = b.INDX
AND a.INDX = c.INDX
AND a.KSPPINM IN ('_optim_peek_user_binds',
'_optimizer_adaptive_cursor_sharing',
'_optimizer_extended_cursor_sharing',
'_optimizer_extended_cursor_sharing_rel');
PARAMETER IS_DEFAULT SESSION INSTANCE
_optimizer_extended_cursor_sharing TRUE UDO UDO
_optimizer_extended_cursor_sharing_rel TRUE SIMPLE SIMPLE
_optimizer_adaptive_cursor_sharing TRUE TRUE TRUE
_optim_peek_user_binds TRUE TRUE TRUE
create table system.test (pk int, data char(100));
-- 500k rows, almost all of them pk=20
INSERT INTO system.test
SELECT CASE WHEN ROWNUM < 20 THEN ROWNUM ELSE 20 END, ROWNUM
FROM DUAL connect by level<= 500000
commit;
create index index on test(pk);
exec dbms_stats.gather_table_stats ('SYSTEM','test');
-- create histogram
exec dbms_stats.gather_table_stats ('SYSTEM','test', method_opt=>'for columns pk size 200');
-- Now run all commands in SQLPLUS
-- bind input 10 , we expect an index scan
var vr1 number;
exec :vr1:=10;
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT()
10 1
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
PLAN_TABLE_OUTPUT
SQL_ID 4csc6dsrzjnc8, child number 0
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 2838211638
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 104 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX1 | 1 | | 3 (0)| 00:00:01 |
Peeked Binds (identified by position):
1 - :VR1 (NUMBER): 10
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
3 - access("PK"=:VR1)
--sql_id is 4csc6dsrzjnc8. -- sql is marked as bind_sensitive
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count(*) from test where pk =:vr1 0 Y Y N
-- bind input 20 this time , we expect an index scan in first execute, as rows returned will fluctuate too much, it will switch to fulltable scan on second execution
var vr1 number;
exec :vr1:=20;
select max(data) max_data, count() from system.test where pk =:vr1;
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT(*)
99999 499981
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
PLAN_TABLE_OUTPUT
SQL_ID 4csc6dsrzjnc8, child number 1
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 1950795681
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 2060 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
|* 2 | TABLE ACCESS FULL| TEST | 499K| 49M| 2060 (1)| 00:00:01 |
Peeked Binds (identified by position):
1 - :VR1 (NUMBER): 20
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - filter("PK"=:VR1)
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count() from test where pk =:vr1 0 N Y N
4csc6dsrzjnc8 select max(data) max_data, count() from test where pk =:vr1 0 Y Y Y
--flush shared pool
alter system flush shared_pool;
--- Now run all commands in TOAD session
-- for binds enter input (10 first, then 20)
-- You wont get a new child cursor, you will always see an index scan and your sql will not be marked as bind_sensitive.
-- enter 10
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT()
10 1
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
SQL_ID 4csc6dsrzjnc8, child number 0
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 2838211638
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 430 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 25000 | 2539K| 430 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX1 | 25000 | | 51 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - access("PK"=TO_NUMBER(:VR1))
--sql_id is 4csc6dsrzjnc8. -- sql is not marked as bind_sensitive
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count(*) from test where pk =:vr1 0 Y N N
--enter 20
select max(data) max_data, count() from system.test where pk =:vr1;
MAX_DATA COUNT()
20 499981
select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
SQL_ID 4csc6dsrzjnc8, child number 0
select max(data) max_data, count(*) from system.test where pk =:vr1
Plan hash value: 2838211638
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 430 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 25000 | 2539K| 430 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INDEX1 | 25000 | | 51 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - access("PK"=TO_NUMBER(:VR1))
--sql_id is 4csc6dsrzjnc8. -- sql is not marked as bind_sensitive
select sql_id,sql_text,child_number,is_shareable,is_bind_sensitive,is_bind_aware from v$sql where sql_id='4csc6dsrzjnc8';
SQL_ID SQL_TEXT CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
4csc6dsrzjnc8 select max(data) max_data, count(*) from test where pk =:vr1 0 Y N N
I think, In TOAD you are parsing sql statement with a DBMS_SQL command before execution.
And according to the Doc ID 2977764.1, "As far as why bind peeking doesn't happen, this is because DBMS_SQL doesn't support it. See this in the known issue Bug 13386678.
In short, DBMS_SQL uses the old RPI interface, which doesn't support bind peeking."
As bind peeking is not fired in TOAD, CBO(Cost based optimizer) makes estimation of returned rows will be 25000, as you may see in the plan.(Total rows(500.000) x 0,05) This is a hardcoded value according to the Doc ID 70075.1 Use of Bind Variables in Queries (Pre-9i).