A statement with bind variables is parsed once and its plan is shared by every execution, whatever values are bound. That saves parsing, but one plan cannot suit every value when the data is skewed: the best plan for a rare value is an index lookup, for a common one a full scan. Oracle handles this in two steps. Bind peeking builds the plan for the values bound at the first hard parse, and adaptive cursor sharing later notices when other values need a different plan. This guide shows both on a skewed column, and the case where the first plan sticks.
Sample Table
100,000 orders: 98,000 DELIVERED, 1,900 SHIPPED, and only 100 NEW, with an index on STATUS and a histogram that records the skew:
Example:
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
status varchar2(10) not null,
amount number(10,2) not null);
insert into demo_orders
select level,
case when mod(level, 1000) = 0 then 'NEW'
when mod(level, 50) = 0 then 'SHIPPED'
else 'DELIVERED' end,
mod(level * 13, 500) + 10
from dual connect by level <= 100000;
commit;
create index demo_orders_status_ix on demo_orders (status);
begin
dbms_stats.gather_table_stats(user, 'DEMO_ORDERS',
method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATUS SIZE 254');
end;
/Output:
Table DEMO_ORDERS created. 100,000 rows inserted. Commit complete. Index DEMO_ORDERS_STATUS_IX created. PL/SQL procedure successfully completed.
Run One Statement with Different Values
The statement runs five times with a bind variable: first for the rare value NEW, then three times for the common value DELIVERED, then NEW again. Each run is parsed, as when an application executes a statement:
Example:
-- no PL/SQL cursor cache in this session:
-- every run is parsed, as from most applications
alter session set session_cached_cursors = 0;
-- the same statement with a bind variable: once for a rare value, then for a common one
declare
l_total number;
procedure run (p_status varchar2) is
begin
execute immediate
'select /* demo_peek */ sum(amount) from demo_orders where status = :st'
into l_total using p_status;
end;
begin
run('NEW');
run('DELIVERED');
run('DELIVERED');
run('DELIVERED');
run('NEW');
end;
/
select child_number as child, executions as execs, plan_hash_value,
is_bind_sensitive as sensitive, is_bind_aware as aware, buffer_gets
from v$sql
where sql_text like 'select /* demo_peek */%'
order by child_number;Output:
Session altered.
PL/SQL procedure successfully completed.
CHILD EXECS PLAN_HASH_VALUE SENSITIVE AWARE BUFFER_GETS
________ ________ __________________ ____________ ________ ______________
0 2 770066352 Y N 728
1 2 284121710 Y Y 728
2 1 770066352 Y Y 102One statement now has three child cursors. In order:
- The first run, for NEW, was hard parsed. The optimizer peeked at the bind value, expected 100 rows from the histogram, and chose the index plan (770066352): child 0.
- The second run, for DELIVERED, reused child 0 and read 98,000 rows through the index. The cursor is marked bind-sensitive (SENSITIVE = Y), so Oracle compared the rows processed with the first run and noticed the large difference.
- The third run was parsed again, and the cursor became bind-aware: the optimizer peeked at DELIVERED and created child 1 with a full scan (284121710). The fourth run reused it.
- The last run, for NEW, got its own bind-aware child 2 with the index plan.
The Peeked Values of Each Plan
The PEEKED_BINDS format shows which value each child's plan was built for:
Example:
set define on
column sql_id new_value peek_sql_id noprint
select distinct sql_id from v$sql where sql_text like 'select /* demo_peek */%';
select * from dbms_xplan.display_cursor('&peek_sql_id', 0, 'BASIC +ROWS +PEEKED_BINDS');
select * from dbms_xplan.display_cursor('&peek_sql_id', 1, 'BASIC +ROWS +PEEKED_BINDS');Output:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select /* demo_peek */ sum(amount) from demo_orders where status = :st Plan hash value: 770066352 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 100 | | 3 | INDEX RANGE SCAN | DEMO_ORDERS_STATUS_IX | 100 | ------------------------------------------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :ST (VARCHAR2(30), CSID=873): 'NEW' 20 rows selected. PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select /* demo_peek */ sum(amount) from demo_orders where status = :st Plan hash value: 284121710 -------------------------------------------------- | Id | Operation | Name | Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | | 2 | TABLE ACCESS FULL| DEMO_ORDERS | 98000 | -------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :ST (VARCHAR2(30), CSID=873): 'DELIVERED' 19 rows selected.
Child 0 was built for NEW, with 100 estimated rows and the index; child 1 for DELIVERED, with 98,000 estimated rows and a full scan.
When the First Plan Sticks
Adaptive cursor sharing can only act when the statement is parsed again. PL/SQL keeps cursors open in its own cache, so a statement executed repeatedly from PL/SQL is not reparsed:
Example:
-- the same runs with the PL/SQL cursor cache in use (the default)
declare
l_total number;
procedure run (p_status varchar2) is
begin
execute immediate
'select /* demo_cache */ sum(amount) from demo_orders where status = :st'
into l_total using p_status;
end;
begin
run('NEW');
run('DELIVERED');
run('DELIVERED');
run('DELIVERED');
run('NEW');
end;
/
select child_number as child, executions as execs, plan_hash_value,
is_bind_sensitive as sensitive, is_bind_aware as aware, buffer_gets
from v$sql
where sql_text like 'select /* demo_cache */%'
order by child_number;Output:
PL/SQL procedure successfully completed.
CHILD EXECS PLAN_HASH_VALUE SENSITIVE AWARE BUFFER_GETS
________ ________ __________________ ____________ ________ ______________
0 5 770066352 Y N 2052All five runs used child 0 and its index plan, peeked for NEW, including the three runs for DELIVERED. The cursor is bind-sensitive but never became bind-aware. This is the classic bind peeking problem: whichever value happens to come first decides the plan for everyone, until the cursor is parsed again, for example after statistics change or the cursor leaves the shared pool.
Things to Know
- A cursor is marked bind-sensitive when the plan's estimate depends on the bind value, typically through a histogram or a range condition.
- V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY show what adaptive cursor sharing measured for each child.
- For a statement whose values always need different plans, separate statements, for example a literal for the rare value, can be clearer than relying on adaptive cursor sharing.
- Bind variables remain the right default: they avoid hard parsing, and most columns are not skewed enough for this to matter.
Related Guides
- How to Use Histograms for Skewed Data in Oracle
- How to Find Slow SQL Statements with V$SQL and V$SQLSTATS
Conclusion
Oracle builds a plan with bind variables for the values it peeks at the first hard parse. When later values process very different row counts, adaptive cursor sharing makes the cursor bind-aware and creates child cursors with plans for each kind of value, but only when the statement is parsed again. Check V$SQL for IS_BIND_SENSITIVE, IS_BIND_AWARE, and the child cursors when a statement with binds is fast for some values and slow for others.
