Understanding Bind Variable Peeking and Its Impact on Oracle Execution Plans

In an OLTP environment using Oracle RAC+DG on version 11g, a sudden performance degradation was observed when a frequently used DELETE statement began consuming excessive resources, prompting an investigation into the underlying Oracle fundamentals.

The article first introduces basic concepts such as how data distribution affects the Cost-Based Optimizer (CBO) decisions, illustrated with a test table where the column name contains 10,000 rows of value 'A' and a single row of value 'B'. Queries using name='A' trigger a full table scan due to the high cardinality, while name='B' benefits from an index range scan.

It then discusses bind variable peeking, a feature introduced in Oracle 9i that inspects the first bind value during hard parsing to generate an optimal execution plan. While peeking can improve plan selection for uniformly distributed columns, it may cause sub‑optimal plans when the column distribution is skewed, because subsequent executions reuse the plan based on the first peeked value.

Experimental results show that with peeking disabled, both name='A' and name='B' use the same (sub‑optimal) plan, whereas with peeking enabled the first execution determines the plan and later executions may suffer if the data distribution changes.

To diagnose bind variable values, three common methods are presented:

Method 1: 10046 Event – Enable level 4 tracing and examine the generated trace file to see the actual bind values.

Method 2: v$sql_bind_capture – Query this view after obtaining the SQL_ID to retrieve captured bind placeholders and their values.

Method 3: AWR Information – Use DBA_HIST_SQLBIND and DBA_HIST_SQLSTAT views to get historical bind data and execution statistics.

Examples of the SQL used in these methods are provided, such as:

select * from t1 where id = :1;

alter system flush shared_pool;

select name, datatype_string, value_string, datatype from DBA_HIST_SQLBIND where sql_id='...';

The article concludes that while bind variables reduce hard parsing overhead, developers must be aware of the potential side effects of bind variable peeking, especially on columns with non‑uniform value distribution, and should leverage the described techniques to monitor and adjust execution plans accordingly.