Why Bind Variables Hurt Oracle Queries: Execution Plans, Histograms & Clustering Factor

1. Basic Knowledge Introduction

Example that changes the execution plan – A test table t1 is created with 10,001 rows (10,000 rows with name='A' and 1 row with name='B'). Queries using the literal value 'A' trigger a full‑table scan because the optimizer estimates that almost the whole table will be returned, while the 'B' query uses an index range scan.

Bind variable peek – When a SQL statement is parsed, Oracle may "peek" at the first bind value and generate an execution plan based on that value. Subsequent executions reuse the same plan even if later bind values differ, which can lead to sub‑optimal plans for skewed data.

Methods to view bind variable values

Enable event 10046 level 4 and examine the trace file.

Query v$sql_bind_capture after obtaining the SQL_ID.

Use AWR views such as DBA_HIST_SQLBIND and DBA_HIST_SQLSTAT to capture historic bind values.

Rolling invalidation – When statistics are gathered, cursors may be marked as "rolling invalidation". Oracle spreads hard‑parses over time using the hidden parameter _optimizer_invalidation_period (default 5 hours) to avoid a parsing storm.

Clustering Factor (CF) – CF measures how closely the order of index keys matches the physical order of table rows. A high CF increases I/O for index range scans, influencing the optimizer's cost calculations.

2. Case Analysis: Sudden Slow DELETE

A production OLTP application on Oracle 11g RAC+DG began experiencing very long execution times for a frequently used DELETE statement with bind variables (key1=:1, key2=:2). The columns have indexes and histograms, but the statement is executed concurrently with high load.

The investigation proceeds in two steps: theoretical background (section 1) and practical analysis of the case.

3. Experiments Demonstrating Bind Variable Peek Using the test table from section 1, the following steps are performed: Create table t1 and populate it with 10,000 'A' rows and 1 'B' row. Run select * from t1 where name='A' and capture the execution plan – it shows a full‑table scan with cost ~49.63. Run select * from t1 where name='B' – the plan uses an index range scan with cost ~2.00. Repeat the queries using bind variables ( select * from t1 where name = :b ) with bind peek disabled. Both executions produce a full‑table scan because the optimizer assumes a 50 % selectivity (computed cardinality = total_rows / NUM_DISTINCT). Enable bind variable peek (default in 11g) and execute the same statements. The first execution (e.g., with bind value 'A') peeks the value and generates a full‑table scan; subsequent executions with 'B' still use the plan based on the first peeked value, resulting in a wrong plan. Flush the shared pool ( alter system flush shared_pool; ) to force a new hard parse. The next execution with 'B' now peeks the correct value and chooses an index range scan. 4. Rolling Invalidation Demonstration A SQL statement executed repeatedly shows two child cursors with identical execution plans but different ROLL_INVALID_MISMATCH flags. By adjusting _optimizer_invalidation_period to 1 minute and repeatedly executing the statement, the cursor is marked as rolling invalidation after the time window expires, causing Oracle to generate a new child cursor on the next hard parse. 5. Clustering Factor (CF) Impact CF is the measure of index key order vs. table row order. A low CF means index entries point to the same data blocks, reducing I/O; a high CF causes many random block reads. Experiment: Create table with two numeric columns, id1 (sequential) and id2 (random), each with a non‑unique index. Gather statistics. id1 index CF ≈ 204, id2 index CF ≈ 99 481 (close to total rows). Query select * from t where id1 between 1 and 1000 – optimizer chooses an index range scan. Query select * from t where id2 between 1 and 1000 – optimizer chooses a full‑table scan because the high CF inflates the I/O cost. Manually set id2 index CF to 200; the optimizer now prefers the index range scan, although actual I/O does not improve proportionally. The cost formulas used by the optimizer illustrate the direct proportionality between CF and the I/O component of an index range scan: <code>IRS Cost = I/O Cost + CPU Cost I/O Cost = Index Access Cost + Table Access I/O Cost Index Access Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL) Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS) </code> 6. Summary of Key Points Bind variable peek can cause the optimizer to lock onto the first bind value, leading to wrong plans for skewed data. Histograms, AWR, and ASH provide insight into execution‑plan changes. Rolling invalidation spreads hard parses over time to avoid CPU spikes. Clustering Factor heavily influences the cost of index range scans; a high CF may make a full‑table scan cheaper. When using bind variables on columns with highly non‑uniform distribution, consider literal values or ensure ACS (adaptive cursor sharing) is enabled (Oracle 11g+).