Oracle SQL plan regression: when a good query suddenly starts doing full scans

A query that returned in 10 milliseconds for months now takes 10 seconds. The application is timing out. CPU on the database host is pinned. The instance is OPEN, the listener responds, existing connections work, but every page load crawls. There is no error in the alert log. Nothing crashed.

This is SQL plan regression. Oracle’s cost-based optimizer chose a new execution plan for a statement that used to perform well. The old plan walked an index and touched a handful of blocks. The new plan does a full table scan and touches millions. The SQL_ID is the same, but the PLAN_HASH_VALUE changed, and BUFFER_GETS per execution jumped by one to three orders of magnitude.

If the statement runs hundreds of times per second, the database drowns in logical I/O. The buffer cache, CPU, and storage I/O all saturate together. One optimizer decision cascades into a system-wide performance emergency within minutes.

The most common trigger is routine statistics gathering. The fix is to force the known-good plan back immediately, then investigate why the optimizer flipped.

What this means

Oracle’s optimizer is cost-based. Every hard parse estimates the cost of available access paths and picks the cheapest. When the inputs to that estimate change, the plan can change. The inputs that matter most:

• Table and index statistics (row counts, distinct values, data distribution)
• Histograms on skewed columns
• Bind variable values at first hard parse (bind peeking)
• Optimizer parameters (OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_FEATURES_ENABLE)
• Object availability (index visibility, partition existence)

When one of these shifts, the optimizer may decide a full table scan is cheaper than an index range scan. Sometimes it is right. On OLTP workloads with selective predicates, it is often catastrophically wrong.

The distinguishing feature of plan regression versus a general load increase is the per-execution metric change. The query is not running more often. It is doing more work each time it runs. BUFFER_GETS / EXECUTIONS for the regressed plan hash is 10x to 1000x higher than for the previous plan hash.

If executions per second are flat but logical reads spiked, you have a plan regression. If executions per second also spiked, you have a workload change.

Common causes

Quick checks

These are read-only and safe to run during an active incident.

-- Find SQL_IDs with multiple plan hashes in the cursor cache
SELECT SQL_ID, COUNT(DISTINCT PLAN_HASH_VALUE) AS plan_count
FROM V$SQL GROUP BY SQL_ID
HAVING COUNT(DISTINCT PLAN_HASH_VALUE) > 1
ORDER BY plan_count DESC;

-- Top SQL by buffer gets per execution (the regression offenders)
SELECT SQL_ID, PLAN_HASH_VALUE, EXECUTIONS,
       ROUND(BUFFER_GETS / NULLIF(EXECUTIONS, 0)) AS gets_per_exec,
       ROUND(ELAPSED_TIME / NULLIF(EXECUTIONS, 0) / 1000) AS ms_per_exec,
       SUBSTR(SQL_TEXT, 1, 120) AS sql_preview
FROM V$SQL WHERE EXECUTIONS > 0
ORDER BY BUFFER_GETS DESC
FETCH FIRST 20 ROWS ONLY;

-- Check for full-scan wait spike
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO / NULLIF(TOTAL_WAITS, 0) / 1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT WHERE EVENT = 'db file scattered read';

-- Logical reads (database work rate)
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME IN ('session logical reads', 'db block gets', 'consistent gets');

-- Active sessions grouped by wait class
SELECT NVL(WAIT_CLASS, 'ON CPU') AS wait_class, COUNT(*) AS sessions
FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND TYPE = 'USER' AND WAIT_CLASS != 'Idle'
GROUP BY NVL(WAIT_CLASS, 'ON CPU')
ORDER BY COUNT(*) DESC;

-- Existing SQL Plan Baselines
SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED, ORIGIN
FROM DBA_SQL_PLAN_BASELINES
WHERE PARSING_SCHEMA_NAME = '<schema>';

How to diagnose it

flowchart TD
    A[Stats gather or bind peek] --> B[Optimizer picks new plan]
    B --> C[Full scan replaces index scan]
    C --> D[BUFFER_GETS per exec jumps 10x-1000x]
    D --> E[db file scattered read waits spike]
    D --> F[Logical reads and CPU spike]
    E --> G[Sessions queue, TPS drops]
    F --> G

1. Identify the regressed SQL_ID. Run the multi-plan query above. Cross-reference with the top-buffer-gets query. The SQL_ID that appears in both lists with a new PLAN_HASH_VALUE and high gets_per_exec is your primary suspect.

2. Compare the old and new plans. For the suspect SQL_ID, pull both plan hashes from V$SQL_PLAN:

-- Show access paths for each plan hash
SELECT PLAN_HASH_VALUE, ID, OPERATION, OPTIONS, OBJECT_NAME, COST
FROM V$SQL_PLAN
WHERE SQL_ID = '&sql_id'
ORDER BY PLAN_HASH_VALUE, ID;

Look for the access path change: TABLE ACCESS BY INDEX ROWID becoming TABLE ACCESS FULL, or an INDEX RANGE SCAN disappearing from the plan.

3. Quantify the regression. Compare BUFFER_GETS / EXECUTIONS and ELAPSED_TIME / EXECUTIONS between the old and new plan hashes. A 10x increase confirms plan regression. A 100x or 1000x increase is typical for full-scan regressions on large tables.

4. Find the trigger. Check what changed:

   • DBA_TAB_STATS_HISTORY: when were statistics last gathered on the affected tables?
   • DBA_TAB_COL_STATISTICS: did a histogram appear or disappear?
   • DBA_INDEXES: is the expected index still VISIBLE?
   • V$SQL_SHARED_CURSOR: why did a new child cursor get created?

5. Confirm with wait events. If db file scattered read dominates the wait profile and User I/O or CPU is the top wait class, this confirms full-scan pressure from the regressed SQL.

6. Check for bind peeking. If the SQL uses bind variables, different bind values can produce different plans on first hard parse. Look at V$SQL_BIND_CAPTURE and child cursor counts. Adaptive cursor sharing may have created a child cursor with a bad plan for a specific bind value, while other children retained the good plan.

Metrics and signals to monitor

Fixes

Stabilize immediately: force the known-good plan

During an active incident, the priority is restoring the old plan before the system saturates further. Two mechanisms:

SQL Plan Baseline from cursor cache. Load the known-good plan into a baseline so the optimizer prefers it:

-- Load the good plan from cursor cache (requires the plan still resident).
-- The loaded plan is ENABLED and ACCEPTED by default, so the optimizer
-- will use it on the next hard parse instead of the regressed plan.
BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => '<sql_id>',
    plan_hash_value => <good_plan_hash>
  );
END;
/

If OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE (the default), the optimizer uses accepted baseline plans instead of any new plan it would otherwise choose. The regressed cursor must age out or be invalidated before the baseline takes effect.

SQL Profile or SQL Patch. For faster intervention when you cannot wait for baseline capture or evolution, a SQL Profile or SQL Patch can force the optimizer toward the known-good plan. These are override mechanisms that apply hints outside the application SQL text. Use these when the old cursor has aged out of the cache and a baseline capture is no longer possible.

Undo the statistics change

If statistics gathering triggered the regression, restore the previous statistics:

-- Restore table statistics to a previous timestamp.
-- WARNING: this invalidates dependent cursors, causing a hard-parse spike
-- on the affected objects. Run during a controlled window, not mid-incident
-- on a system already CPU-bound.
BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname          => '<schema>',
    tabname          => '<table>',
    as_of_timestamp  => '<timestamp_before_regression>'
  );
END;
/

Check DBA_TAB_STATS_HISTORY for available restore timestamps.

Remove a problematic histogram

If a new histogram caused the cardinality estimate to flip, regather statistics without it:

-- Regather stats without histogram on the offending column.
-- WARNING: like RESTORE_TABLE_STATS, this invalidates dependent cursors.
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname   => '<schema>',
    tabname   => '<table>',
    method_opt => 'FOR COLUMNS <column> SIZE 1'
  );
END;
/

SIZE 1 means no histogram on that column. The optimizer falls back to uniform distribution estimates.

Tradeoffs

Forcing a plan with a baseline or profile locks the optimizer to that plan. This prevents regressions but also prevents the optimizer from finding genuinely better plans as data distribution evolves. For high-frequency OLTP SQL where predictability matters more than incremental optimization, this tradeoff is worth it. For reporting or batch SQL that benefits from adaptive plans, manage baselines with periodic evolution instead of pinning them.

Prevention

• Capture SQL Plan Baselines for critical SQL. Enable OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES at session or system level (for example, through the application logon trigger), or manually load baselines for known-critical SQL_IDs using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. New plans are captured but not accepted until they pass verification.
• Stage statistics changes. Gather statistics with PUBLISH set to FALSE, then compare pending stats against published using DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING. Publish only if the diff is safe. The 19c DBMS_STATS reference documents the pending-statistics comparison function without an edition restriction; verify installed-package availability if you are on a constrained edition.
• Monitor buffer_gets per execution. Track this ratio for your top 20 SQL_IDs and alert on changes greater than 10x. This is the most underdeployed signal in Oracle monitoring.
• Lock plans for high-frequency SQL. Use FIXED baselines (DBMS_SPM.ALTER_SQL_PLAN_BASELINE with FIXED set to YES) for SQL that must never change plan without an explicit change review.
• Test upgrades with plan stability in mind. After a version upgrade, use OPTIMIZER_FEATURES_ENABLE to isolate optimizer behavior changes. Capture baselines before the upgrade.
• Review the automatic SPM evolve task. Oracle 19c includes SYS_AUTO_SPM_EVOLVE_TASK and the High Frequency SPM Evolve Advisor Task. Edition support and automatic evolution capabilities vary: SQL Plan Management is available in Enterprise Edition, while Standard Edition 2 has reduced capabilities; verify the installed edition and task state before relying on automation.

How Netdata helps

Plan regression is a per-execution problem that becomes visible as a system-wide spike. Netdata correlates the system-level signals that confirm the cascade:

• Logical reads per second (session logical reads from V$SYSSTAT) surfaces as a sudden 2x to 10x spike with no corresponding throughput increase, distinguishing a plan flip from a workload increase.
• db file scattered read wait time trending up on an OLTP system is the full-scan signature. Correlating this with a logical reads spike confirms the regression pattern.
• CPU utilization rising while TPS stays flat or drops indicates the database is burning cycles on unproductive scans rather than useful work.
• Active sessions versus CPU core count shows queue depth growing as regressed queries pile up behind the full scans.
• Anomaly detection on logical reads, physical reads, and CPU baselines flags the deviation within seconds of the plan flip, before the application starts timing out.
• Per-second granularity means you can see the exact minute the regression started and correlate it with statistics job completion or a parameter change.

Oracle Database monitoring with Netdata brings these signals together with per-second metrics and anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle hard parse storm: literal SQL, bind variables, and shared pool churn
• Oracle ’library cache: mutex X’ waits: parsing pressure and cursor contention
• Oracle lock contention cascade: one idle session that stalls the whole application