Oracle Query Plan Stability: Histograms, Bind Variables, ACS and Plan Regression

A SQL statement running quickly yesterday and slowly today does not imply that its text changed. Oracle can choose a different execution plan for the same SQL when data distribution, statistics, bind values or environment conditions change. Plan stability is therefore less about forcing one plan forever and more about making plan changes explainable, measurable and reversible.

Cardinality estimation is central

The optimizer chooses join order, access path and join method from row-count estimates. If actual cardinality differs substantially from the estimate, even a sound cost model compares the wrong alternatives. Selectivity, column statistics, number of distinct values and data distribution are therefore the first layer of plan analysis.

A database histogram can represent skew that a uniform model would miss. Its presence does not automatically improve a plan; a stale or unrepresentative histogram can also distort estimates.

Bind variables and bind peeking

Bind variables improve cursor reuse, but different bind values do not necessarily have similar selectivity. Bind peeking lets the value observed during an initial parse influence the estimate. On a skewed column, a plan selected for a rare value may be inappropriate for a very common one.

The useful question is therefore not whether binds are universally good or bad, but how their value distribution interacts with predicates and access paths.

Adaptive Cursor Sharing

Adaptive Cursor Sharing allows bind-sensitive SQL to use different child cursors for different selectivity ranges. This relaxes the assumption that every bind value should share one plan, but makes cursor diagnostics more important. Multiple child cursors under one SQL_ID are not inherently a defect; the relevant issue is why they diverged and which value ranges use each plan.

Statistics change and plan regression

Statistics refreshes, data growth, partition skew or a new index can alter the optimizer's cost comparison. A new plan may carry a lower estimated cost yet perform worse under the real workload. That is the practical form of plan regression.

Diagnosis should not rely on EXPLAIN PLAN alone. Runtime cursor statistics, estimated-versus-actual rows, wait events, logical/physical I/O and elapsed/CPU time provide stronger evidence. Expression-level details such as the conversions discussed in Safe Numeric Conversion in Oracle SQL can also affect selectivity and index usability.

When plan control is appropriate

SQL Plan Management and related mechanisms can protect verified plans for critical SQL. Pinning a plan without understanding the estimation problem, however, can hide the root cause. Data distribution can change until the once-good plan is no longer suitable.

I therefore separate diagnosis from control: first establish why the optimizer chose the plan, then apply the degree of plan control justified by workload criticality. A low-risk reporting query and a high-throughput transactional query do not require the same stability policy.

Production diagnostic sequence

I compare SQL_ID, plan hash value and time-series changes in elapsed time, CPU and buffer gets. I then inspect cardinality-estimation errors and bind distribution, followed by statistics, histograms, indexes and optimizer-setting changes. If several child cursors exist, their creation reasons are separated. A plan change is not treated as a regression until runtime evidence shows that it actually degraded the workload.

Plan stability in Oracle is not the elimination of optimizer choice. It is the discipline of making those choices observable, testable and reversible. Oracle Database and PL/SQL provides the wider architectural context for that analysis.