Oracle Bind Variable Peeking and Plan Instability

Bind variables improve cursor sharing, but a plan chosen for one bind value can perform poorly for another. Oracle calls the first-value optimization behavior bind variable peeking.

How peeking affects a plan

During a hard parse, the optimizer can inspect the current bind values and estimate selectivity as though literals were present. This helps when the first value is representative. It can hurt when data is skewed, histograms exist, or a partitioned table has very different statistics from one partition to another.

The classic failure pattern is simple: a cursor is first parsed with a value that favors a full scan, then reused for values that would be much faster with an index—or the reverse. A newly created, nearly empty partition can also produce a plan that is unsuitable for older, heavily populated partitions.

Inspect the executed cursor

Do not rely only on EXPLAIN PLAN. Inspect the cursor that actually ran:

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id       => '&sql_id',
    child_number => NULL,
    format       => 'ALLSTATS LAST +PEEKED_BINDS +PARTITION'
  )
);

Also review the child cursors in V$SQL, including IS_BIND_SENSITIVE, IS_BIND_AWARE, plan hash values, and execution statistics.

Practical remedies

• Gather representative table, column, and partition statistics.
• Review whether histograms are appropriate for the workload.
• Allow adaptive cursor sharing to create bind-aware child cursors where applicable.
• Rewrite SQL when one statement is serving fundamentally different access patterns.
• Use SQL Plan Management or a SQL patch when a stable, tested plan is required.
• Avoid hidden parameters and shared-pool flushing as permanent fixes.

Reference

Oracle explains the behavior in Adaptive Cursor Sharing, and documents DBMS_XPLAN.DISPLAY_CURSOR for viewing cached execution plans.