Optimizer Truth Serum for Oracle: DBMS_XPLAN, Bind Peeking, Adaptive Plans, and Keeping Plan Hashes Stable

Hook: You upgrade Oracle, rerun a “harmless” query, and your P99 latency triples. Same SQL text. Same indexes. Different plan hash value. Welcome to the optimizer’s funhouse—where bind peeking, adaptive plans, and stale stats quietly rewrite your fate. Let’s strip away the magic and get surgical.

Why this matters

In Oracle, a few “invisible” features decide whether your service hums or melts:

Bind peeking can lock in a bad plan for skewed values.
Adaptive cursor sharing and adaptive plans can spawn child cursors and swap plan shapes mid-flight.
Plan hash values drift across releases unless you pin the shape.

If you run OLTP with heavy bind usage, or do rolling upgrades, you need a disciplined way to see (DBMS_XPLAN), diagnose (ACS flags, bind selectivity), and stabilize (SPM, profiles/patches, adaptive controls).

Concepts & Architecture — The moving parts

DBMS_XPLAN (your single source of truth)

Use DBMS_XPLAN.DISPLAY_CURSOR to print the actual execution plan from the cursor cache.
Add the right format options to reveal what really happened: row estimates vs actuals, peeked binds, adaptive sections, outline data.

Key format flags (combine as needed):
'ALLSTATS LAST +PEEKED_BINDS +ADAPTIVE +OUTLINE +ALIAS +NOTE'

Bind peeking

On first hard-parse, Oracle peeks bind values to estimate selectivity.
If data is skewed (e.g., :b1 = 'VIP' returns 1 row vs 'COMMON' returns 1M rows), the first bind can bake in the wrong access path.
Histograms mitigate—but can also destabilize—if not curated.

Adaptive Cursor Sharing (ACS)

Oracle monitors executions and, when bind selectivity differs materially, spawns child cursors with alternative plans keyed to selectivity ranges.
Great when used correctly, chaotic if you’re blind to it. Inspect V$SQL_SHARED_CURSOR reasons.

Adaptive Plans / Adaptive Statistics

Adaptive plans can switch join methods or set operations at runtime.
Adaptive statistics (older 12cR1 behavior) gathers extra stats on the fly (e.g., dynamic sampling/feedback). Helpful in analytics, risky in OLTP.
Since 12.2+, controls split: optimizer_adaptive_plans vs optimizer_adaptive_statistics.

Plan hash value vs full plan hash

PLAN_HASH_VALUE summarizes the plan shape.
Minor changes (new ops names, projections) or version differences can alter it even if performance feels similar.
FULL_PLAN_HASH_VALUE is stricter; both can change across releases. Don’t rely on hash alone—stabilize the plan.

Real Examples (SQL)

1) Print exactly what ran (plus the truth you usually miss)

-- Last execution stats, peeked binds, adaptive detail, and outline/hints:
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id => '&&sql_id',
    cursor_child_no => 0,
    format => 'ALLSTATS LAST +PEEKED_BINDS +ADAPTIVE +OUTLINE +ALIAS +NOTE'
  )
);

What you’ll see:

A-Rows vs E-Rows to expose misestimation.
Peeked Binds to confirm the first value that shaped the plan.
Outline Data: the implicit hint-set Oracle used (gold for plan pinning).
Adaptive sections marking deferred choices.

2) Catch bind-driven plan drift (child cursors)

-- Which children and why?
SELECT child_number, plan_hash_value, parsing_schema_name,
       is_bind_sensitive, is_bind_aware
FROM   v$sql
WHERE  sql_id = '&&sql_id'
ORDER  BY child_number;

-- Reasons for child cursor proliferation:
SELECT child_number, reason
FROM   v$sql_shared_cursor
WHERE  sql_id = '&&sql_id';

Interpretation:
If you see IS_BIND_SENSITIVE='Y' and multiple children with reasons like BIND_MISMATCH or LITERAL_MISMATCH, ACS is at work. Expect different plans for different bind selectivity classes.

3) Lock the plan shape with SQL Plan Baselines (SPM)

-- Capture the current plan from cursor cache into a baseline:
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&&sql_id');
  DBMS_OUTPUT.PUT_LINE('Loaded: ' || n);
END;
/
-- Verify baseline:
SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%your statement text%';

Result: Future hard-parses prefer the accepted baseline, keeping the shape (and hash) consistent across patchsets/upgrades—until you choose to evolve.

4) Use a SQL Patch or Profile when you need a nudge, not a cage

-- Create a SQL Patch to inject a hint (e.g., force NL join):
DECLARE
  h CLOB := q'[
    /*+ USE_NL(t1 t2) LEADING(t1 t2) CARDINALITY(t2 100) */
  ]';
BEGIN
  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id       => '&&sql_id',
    hint_text    => h,
    name         => 'patch_force_nl',
    description  => 'Stabilize join method for skewed binds'
  );
END;
/

When to use: Emergency stabilization without committing to a full baseline.

Best Practices (that actually hold up in prod)

See everything first

Always start with DBMS_XPLAN.DISPLAY_CURSOR('ALLSTATS LAST +PEEKED_BINDS +ADAPTIVE +OUTLINE').
Compare A-Rows vs E-Rows to decide whether the problem is stats or plan shape.

Tame bind peeking & skew

Create frequency histograms only where skew exists and is query-relevant. Blanket histograms = churn.
For high-skew predicates (status, country, tenant_id), consider query rewrites (e.g., CASE splits for “hot” values) or hybrid literals for the truly hot path.
Let ACS do its job, but monitor child cursor explosion.

Control adaptivity

OLTP: prefer optimizer_adaptive_plans = TRUE, but keep optimizer_adaptive_statistics = FALSE to avoid runaway sampling/feedback.
Analytics/reporting: cautiously enable adaptive stats where cardinality is chronically misestimated.

Stabilize before and after upgrades

Before upgrade:
- Capture baselines: DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
- Record SQL_ID, PLAN_HASH_VALUE, and outline data for critical SQL.
During testing:
- If needed, set OPTIMIZER_FEATURES_ENABLE at session/service level to the previous release, then gradually lift.
After upgrade:
- Compare plan hashes; if drifted, evolve baselines deliberately, not accidentally.
- Use SQL patches for surgical overrides, then replace with baseline once confident.

Keep plan hash stable (or at least the shape)

Rely on SPM baselines to freeze shape across releases.
Avoid fragile hints in application SQL; keep control in SPM/patches.
Watch for innocuous DDL (e.g., adding a function-based index) that can change the chosen plan; re-baseline critical SQL after structural changes.

Maintenance discipline

Stats: set sensible granularity and method_opt (collect histograms only where selective).
Purge: avoid flushing the shared pool in production—it destroys cursor history that ACS/SPM benefit from.
Measure: track sql_id → p99 latency → plan_hash_value over time. If you don’t graph it, you will miss the drift.

Common Pitfalls (and how to avoid them)

“We turned on histograms everywhere.”
Noise in estimates, plan churn. Only add histograms where predicates and skew matter.
“ACS will save us.”
It helps, but uncontrolled child cursor growth becomes its own problem. Monitor V$SQL_SHARED_CURSOR.
“We trust the plan hash value as truth.”
Hashes change across versions/patches. Use baselines to enforce shape, and compare outlines, not just numbers.
“One-size adaptive settings.”
OLTP and analytics need different knobs. Split services and set parameters per workload.
“We hint everything in code.”
You’re handcuffing future upgrades. Prefer SPM and SQL patches to keep leverage.

Comparison Table — What tool for which job?

Need	Use	Why
See the exact plan, binds, estimates vs actuals	`DBMS_XPLAN.DISPLAY_CURSOR('ALLSTATS LAST +PEEKED_BINDS +ADAPTIVE +OUTLINE')`	Truth with context
Different plans for different bind selectivities	Adaptive Cursor Sharing (ACS)	Handles skew without code changes
Freeze plan shape across releases	SQL Plan Baselines (SPM)	Version-proof shape control
Quick surgical fix (hints without code change)	SQL Patch (or Profile)	Low-friction stabilization
Roll back optimizer behavior during testing	`OPTIMIZER_FEATURES_ENABLE`	Safety net for upgrades
Reduce misestimation for skewed columns	Targeted histograms	Better selectivity, fewer surprises

Conclusion & Takeaways

Observe first with DBMS_XPLAN; don’t argue with estimates—compare them to actuals.
Triage skew with targeted histograms and let ACS branch plans when it helps.
Stabilize critical SQL with SPM baselines; use patches/profiles as temporary crutches.
Control adaptivity per workload; OLTP and analytics want different settings.
Plan for upgrades by capturing baselines beforehand and evolving on your terms.

If you make these steps muscle memory, plan hash “surprises” turn into planned, reversible changes—not outages.

Image prompt

“A clean, modern Oracle optimizer diagnostics diagram showing a query’s path: bind peeking → adaptive cursor sharing → execution plan alternatives → SQL Plan Baseline enforcement. Minimalist, high-contrast, 3D isometric style with labeled stages and arrows.”

Data/ML Engineer Blog