A SQL ID can dominate Oracle DB time without having a bad plan, and a new plan hash can appear without causing the incident. Root cause begins only when the investigator proves which child cursor ran, how much work it performed, and why its behavior changed.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

The first Oracle article reduced an incident to database time, foreground waits, and SQL IDs. That attribution is a lead, not a verdict. A statement can rank first because it ran more often, processed more data, waited behind another session, or inherited storage pressure.

Oracle separates a parent cursor from its executable children. One SQL_ID can have multiple children because optimizer environments, object metadata, bind selectivity, or compatibility checks differ. Their plans and runtime profiles can differ.

After collectors preserve those distinctions, the LLM can connect deployment, child-cursor, estimate, bind, and workload evidence. It cannot replace the measurements.

The Problem

Three shortcuts routinely manufacture a plan-regression story:

  • comparing a good historical plan with whatever EXPLAIN PLAN produces now;
  • treating PLAN_HASH_VALUE as a complete description of execution behavior;
  • comparing cumulative elapsed time without normalizing executions, rows, and interval coverage.

EXPLAIN PLAN describes what Oracle would choose in the explain session. Binds and optimizer environment can make the executed cursor differ. A plan hash aids comparison but omits runtime context; full plan hashes are not comparable across releases.

An actual plan change still does not prove regression. It may suit a selective bind or larger result set. The same plan can slow when it performs more starts, reads more rows, spills, or waits for resources.

Can we prove that a specific execution path became less efficient for comparable work, identify the mechanism, and change it without freezing the wrong behavior?

Build the SQL Regression Proof Chain

flowchart TD
    A[ranked SQL ID from incident] --> B[scope child cursors plans and execution window]
    B --> C[normalize runtime deltas per execution and row]
    C --> D[retrieve the actual executed plan]
    D --> E[compare estimated and actual row flow]
    E --> F[correlate binds statistics environment and changes]
    F --> G[rank regression mechanisms and contradictions]
    G --> H[DBA verification with representative inputs]
    H --> I[reversible plan code or statistics response]
    I --> J[application and database validation]

The evidence pack should keep four layers separate:

OBSERVATION  child 3 increased buffer gets per execution in the incident window
HYPOTHESIS   a cardinality error changed the join order
MISSING      actual rows, starts, predicates, bind class, and statistics history
ACTION       capture scoped runtime plan statistics and test representative binds

Confidence belongs to hypotheses, not observations. The LLM should cite evidence identifiers and expose contradictions.

Symptoms

Evidence patternLeading interpretationWhat remains unproven
Same SQL ID, new child and plan hash, higher gets per executionPossible access-path regressionComparable binds and rows
Same plan hash, higher elapsed time, stable getsWait or resource changeSQL is innocent
Same plan, more rows and startsWorkload-shape or data-volume changeOptimizer regression
Estimate and actual rows diverge early, then join work expandsCardinality mechanismWhich statistics assumption failed
Several plans serve different bind ranges efficientlyAdaptive cursor sharing behaving as designedOne plan should be forced
Total SQL time rises while time per execution is stableFrequency-driven load growthIndividual executions regressed
Parse load, child count, and invalidations riseCursor lifecycle or sharing problemExecution plan is the primary cost

First Five Checks

1. Identify the execution, not only the SQL ID

Record SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, parsing schema, service, module, action, instance, PDB, optimizer environment, load and activity times, and incident boundaries. Preserve RAC instance and multitenant container identity.

Use matched V$SQL snapshots or approved equivalents. Its values are cumulative per child, so calculate timestamped deltas. Cursor aging, reload, invalidation, restart, and reset can break the interval. Do not merge children that share SQL text.

AWR plan and SQL history require Diagnostics Pack. Real-time SQL Monitoring requires Tuning Pack and Diagnostics Pack. Dynamic views still need least-privilege access and may expose SQL or binds; entitlement and data authorization are separate gates.

2. Normalize before comparing plans

For each child and interval, calculate at least:

elapsed_per_execution = delta_elapsed_time / delta_executions
cpu_per_execution     = delta_cpu_time / delta_executions
gets_per_execution    = delta_buffer_gets / delta_executions
reads_per_execution   = delta_disk_reads / delta_executions
rows_per_execution    = delta_rows_processed / delta_executions
gets_per_row          = delta_buffer_gets / delta_rows_processed

Guard every denominator. Zero executions, partial fetches, parallel accounting, recursive work, and mixed bind classes can invalidate averages. Compare business throughput and row distributions: longer runtime for many more rows may be efficient, while an average can hide a slow percentile.

3. Retrieve the executed cursor plan

Use DBMS_XPLAN.DISPLAY_CURSOR for the relevant child while cached. Include runtime statistics only when collected. An approved test can use gather_plan_statistics and ALLSTATS LAST; setting STATISTICS_LEVEL=ALL system-wide adds broader overhead and should not be the incident default.

Do not send an unredacted plan automatically. SQL, predicates, objects, projections, and peeked binds can reveal sensitive data or design. Preserve a restricted original, create a sanitized copy, and record transformations.

4. Find the first consequential estimate error

Compare optimizer-estimated rows with actual rows at each operation. Include Starts: total actual rows from an operation that started many times means something different from rows per start. Work from the leaves upward and identify the earliest material divergence that changes downstream join order, join method, access path, memory demand, or repeated work.

Then test a mechanism. Beyond stale statistics, skew can require a histogram; correlated predicates can require column-group statistics; expressions can require expression statistics. Bind peeking may make the first value influential. Adaptive cursor sharing can legitimately create children for different selectivity ranges.

Captured or sampled binds may not represent the incident execution. Store typed ranges or hashes, then verify representative values through an approved test.

5. Correlate the plan with change and wait evidence

Align child creation, invalidation, plan appearance, statistics, schema, deployment, optimizer, routing, and incident transitions. Compare wait composition by child where licensed evidence permits.

If gets per execution rose with an access-path change, the plan is a strong lead. Stable gets with rising User I/O across many SQL IDs favors storage or cache conditions. Dominant Application waits may mean blocking. Explain supporting and contradicting evidence.

Decision Tree

flowchart TD
    A[SQL ID ranks high in incident] --> B{work per execution increased}
    B -->|no| C{execution frequency increased}
    C -->|yes| D[classify workload growth or retry amplification]
    C -->|no| E[inspect waits blocking and evidence coverage]
    B -->|yes| F{executed child or plan changed}
    F -->|no| G[inspect row volume starts spills cache and resources]
    F -->|yes| H{inputs and returned rows are comparable}
    H -->|no| I[segment by bind selectivity and workload shape]
    H -->|yes| J[compare estimates actual rows and predicates]
    J --> K{mechanism is reproducible}
    K -->|no| L[collect missing runtime statistics and change history]
    K -->|yes| M[test the smallest reversible correction]

In Practice

Oracle’s V$SQL_PLAN stores plans per child cursor and describes PLAN_HASH_VALUE as a convenient numerical plan representation. Oracle’s execution-plan guidance also states that optimizer inputs include statistics, binds, initialization parameters, and schema changes, and that an explained plan can differ from the actual plan. The documented behavior supports child-level attribution, not SQL-ID-level certainty.

DBMS_XPLAN.DISPLAY_CURSOR displays a loaded cursor and can add I/O and memory statistics when basic plan statistics were collected. V$SQL_PLAN_STATISTICS_ALL exposes operation-level starts, output rows, reads, buffer gets, and elapsed time. These are the mechanics behind estimate-versus-actual analysis; the LLM should never invent missing actual rows.

Oracle documents that histograms improve estimates for skewed columns and that extended statistics can model correlated columns and expressions. Its adaptive cursor sharing guidance explains how bind-sensitive statements can become bind-aware and use different plans for different selectivity ranges. Multiple plans are therefore evidence to explain, not an anomaly by definition.

SQL Plan Management separates accepted, unaccepted, enabled, and fixed baselines. Oracle’s SPM overview describes plan evolution as verification before acceptance. By contrast, a SQL profile supplies auxiliary optimizer statistics and does not pin one plan. SQL profiles and SQL Tuning Advisor are Tuning Pack features. Verify edition, deployment, and licensing before selecting any control.

Remediation Options

Proven conditionCandidate responseMain risk
Workload frequency or retries grewThrottle, deduplicate, or correct the callerHides necessary demand
Statistics no longer represent the dataTest scoped statistics correction and publish safelyReplans unrelated statements
Skew or correlation breaks estimatesTest histogram or extended statisticsMore parse sensitivity and maintenance
Known accepted plan is reproducibly betterUse reviewed SQL Plan Management workflowFreezes a plan past its useful life
SQL formulation causes unnecessary workChange SQL and test representative bindsSemantic or compatibility regression
Access path needs a durable structureAdd or redesign an index after write-cost reviewDML, storage, and maintenance cost
Resource waits dominate with stable workFix blocking or platform constraintPlan intervention changes nothing

Do not gather schema statistics, flush the shared pool, fix a baseline, or accept an advisor recommendation merely to make the plan change. Each action changes scope and risk. A fixed baseline is a control, not a diagnosis; it can also block future improvement.

Rollback Plan

Define rollback before the test. Preserve the previous application artifact, accepted baseline state, statistics metadata, DDL, parameter values, and a representative validation workload. For a newly introduced plan baseline, use the reviewed DBMS_SPM workflow to disable or remove that intervention and restore the previously approved accepted set. For code, index, or statistics changes, use their tested change-specific reversal.

Do not describe shared-pool purge as rollback. It discards cached state, affects other sessions, and does not restore the prior optimizer inputs. Stop the change if application latency, errors, DB time per business operation, buffer gets per execution, or a workload-specific guardrail worsens. After reversal, confirm the executing child and plan; configuration rollback without execution proof is incomplete.

Where It Breaks

Failure modeWhy the conclusion failsBetter evidence
New plan hash equals regressionDifferent inputs may need a different planComparable bind class, rows, and normalized work
Same plan hash equals stable SQLRuntime volume, starts, spills, cache, and waits can changeActual row flow and resource evidence
Lower cost means fasterCost is an optimizer comparison unit, not elapsed timeMeasured runtime under representative inputs
Fresh explain output represents the incidentEnvironment and binds may differExecuted child plan from the incident
High total elapsed time means slow executionsMore executions can raise the totalInterval deltas and per-unit metrics
Large estimate error alone proves root causeAn error matters only if it changes consequential workFirst divergence and downstream effect
One captured bind represents productionCapture can be sampled, incomplete, or sensitiveApproved representative classes and redaction
LLM recommends a profile or baselineIt may conflate estimate correction with plan controlDBA choice with licensing and rollback checks

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

  • No Raw Customer Data: The LLM must not receive bind capture values from V$SQL_BIND_CAPTURE, literal SQL text, or row data. Send SQL_ID, force-matching signature, plan hash values, wait-event timings, and DB Time attribution only.
  • No Autonomous Production Execution: The LLM cannot run ALTER SYSTEM, ALTER SESSION on other sessions, kill sessions, apply SQL profiles or baselines, or gather statistics. It proposes; a human authorizes, applies, validates, and holds the rollback condition. Note also that AWR and ASH access carries Diagnostics Pack licensing implications — confirm entitlement before any collector reads them.
  • No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
  • No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
  • No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

  • Problem: A top SQL ID, changed plan hash, or optimizer estimate is suggestive but cannot establish which execution regressed or why.
  • Solution: Scope the child cursor and interval, normalize runtime work, inspect the executed plan, compare estimated with actual row flow, and test bind, statistics, environment, and change mechanisms.
  • Proof: Reproduce the mechanism with representative inputs, show that the intervention reduces application latency and database work without harming other workload classes, then verify the executing child after change.
  • Action: Add child-cursor snapshots and a redacted DISPLAY_CURSOR artifact to the evidence-pack schema, with explicit fields for collection scope, runtime-statistics availability, licensing, and contradictions.

The next article tests the opposite conclusion: when SQL evidence is stable, investigate concurrency, redo, RAC coordination, storage, and cloud infrastructure without forcing a query-tuning narrative.

Sources