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.