Oracle Query Plan Stability: Histograms, Bind Variables, ACS and Plan Regression
How Oracle can choose different plans for the same SQL as cardinality estimates, bind peeking, histograms, adaptive cursor sharing and statistics change, with a focus on diagnosing 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.
A SQL Plan Management baseline controls the accepted plan set; it does not directly repair a faulty cardinality estimate, a missing index, or an excessively long transaction. Plan stability and the correctness of query results against business rules require separate verification. When the data distribution changes, controlled comparisons must include representative bind values and realistic query volumes.
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.
For an Oracle execution-plan investigation, record SQL_ID, CHILD_NUMBER, the plan hash value and relevant bind values in the same execution context. Actual row statistics displayed by DBMS_XPLAN.DISPLAY_CURSOR are meaningful only for executions where the required statistics were collected; estimated cardinality and observed cardinality must remain distinct. Adaptive Cursor Sharing can create multiple child cursors for different bind selectivities, so one SQL_ID does not imply one immutable execution plan.
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.
Comparison by Selectivity Group
Evaluating a plan with one bind value can hide behavior at the extremes of the distribution. Representative bind groups producing narrow, medium and broad result sets are compared on the same data and statistics version. Returned rows, logical reads and execution count accompany elapsed time. Even an unchanged plan hash can coexist with different data volume, waits and cache state.
Investigation can begin by reading existing execution information; flushing shared caches or enabling system-wide tracing in production is not a prerequisite. Features such as AWR and ASH require consideration of version, privileges and licensing conditions. One observation does not justify forcing a plan across all bind values.
References
