Oracle problems

Oracle SQL Plan Changed: How to Find the Old Plan, Why It Flipped, and How to Get It Back

2 November 2026

A report that took two seconds for a year takes three minutes since this morning, and nobody changed anything. Somebody did, or the data did, and the optimizer chose a new plan. The job is to see the old plan, understand why it lost, and put it back without guessing.

Confirm that the plan changed

Every plan has a hash. While the old cursor is still in the shared pool, both are visible, with their cost per execution:

SELECT sql_id, plan_hash_value, child_number, executions,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1e3) AS ms_exec,
       ROUND(buffer_gets  / NULLIF(executions, 0))       AS gets_exec,
       TO_CHAR(last_active_time, 'DD HH24:MI')           AS last_act
FROM   gv$sql
WHERE  sql_id = '&sql_id'
ORDER  BY last_active_time DESC;

Two rows with two PLAN_HASH_VALUEs and a tenfold difference in GETS_PER_EXEC is the whole diagnosis. The old plan is gone from the cache after a flush, a restart, or a few busy hours. Oracle keeps older plans in DBA_HIST_SQL_PLAN and DBA_HIST_SQLSTAT, which belong to the Diagnostics Pack, an extra license on Enterprise Edition and not available on Standard Edition. Without the pack, you keep your own copy of V$SQL and V$SQL_PLAN on a schedule, or use a tool that does: Scops records SQL statistics and plans every 10 minutes, keeps the history of each plan for 15 days, and its Insights page lists the statements that run slower on their current plan than on their other plans, with every plan and its cost per run.

Compare the two plans

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'ALLSTATS LAST'));
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 1, 'ALLSTATS LAST'));

The second argument is the child number from the query above. With ALLSTATS LAST the plan shows estimated and actual rows for each step, when the statement ran with STATISTICS_LEVEL = ALL or the GATHER_PLAN_STATISTICS hint. Look for the step where E-Rows and A-Rows differ by a factor of a hundred: the optimizer chose the plan on that wrong estimate, and everything below it follows.

Why plans flip

The optimizer did not change its mind for no reason. One of these did:

Cause	How to see it
Statistics were gathered	`LAST_ANALYZED` on the tables of the plan, below. The nightly job gathered on a table at its emptiest, or a partition at its fullest
Bind peeking and adaptive cursor sharing	`IS_BIND_SENSITIVE` and `IS_BIND_AWARE` in `V$SQL`: the plan was chosen for the first value and reused for the others
Adaptive plans and statistics feedback	`IS_RESOLVED_ADAPTIVE_PLAN` and `IS_REOPTIMIZABLE` in `V$SQL`: 12c changes the plan after it sees the real row counts
A new or dropped index, a parameter, a new patch	`DBA_OBJECTS.CREATED`, `V$PARAMETER`, `DBA_REGISTRY_SQLPATCH`
The data crossed a threshold	Nothing changed except the rows: a table doubled, a status column that had 2% of one value now has 40%

SELECT DISTINCT p.object_owner, p.object_name,
       t.last_analyzed, t.num_rows, t.stale_stats
FROM   gv$sql_plan p
JOIN   dba_tab_statistics t ON t.owner      = p.object_owner
                           AND t.table_name = p.object_name
WHERE  p.sql_id = '&sql_id'
AND    p.object_type LIKE 'TABLE%'
ORDER  BY t.last_analyzed DESC;

Get the old plan back, in this order

Fix it in minutes with a SQL plan baseline, while the old plan is still in the cache. A baseline tells the optimizer which plans are accepted for a statement; it is part of Enterprise Edition and needs no pack:
```
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id          => '&sql_id',
         plan_hash_value => &old_plan_hash_value);
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/
```
The next parse uses the old plan. New plans are still captured for the statement, but not used until you evolve them.
If statistics did it, restore them. Oracle keeps the previous statistics for 31 days by default:
```
BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname         => 'APP',
    tabname         => 'ORDERS',
    as_of_timestamp => SYSTIMESTAMP - INTERVAL '1' DAY);
END;
/
```
Then make the gathering job stop doing it again: lock the statistics of a table that is emptied and filled by a batch, or gather with the right sample at the right time.
Make the statement forget the bad plan. After either fix, purge its cursor so it is parsed again now, not when it ages out: DBMS_SHARED_POOL.PURGE('address,hash_value', 'C') with the values from V$SQLAREA.
Fix the estimate if neither the stats nor a baseline is the real answer: a histogram on the skewed column, extended statistics on correlated columns, or a rewritten predicate. SQL profiles from the SQL Tuning Advisor need the Tuning Pack.

See it coming

A plan change is visible the moment the new child cursor is created, hours before the first complaint. Radar Engine, the detection layer of Scops, reports it as soon as the statement matters. A SQL finding needs 15 minutes of database time an hour, from the SQL of your applications, so a statement that nobody feels stays quiet.

Detector	Clock	When it fires
SQL on a new plan	Live, every 5 minutes	A new plan: Warning. Three times slower, and it adds a full active session: Critical.
SQL running slower	Live	Three times slower per run: Warning. Five times, and it adds a full active session or holds two: Critical.
SQL slower per run	Hourly	The last hour 1.5 times slower than the five before: Warning. Not if it was as slow at this hour a day or a week ago.
SQL slowing down	Daily	1.5 times slower per run for several days, at 100 ms and 30 runs or more: Warning.

Under Plan Changes, the Insights page of Scops lists the statements that are at least 20% slower on their current plan than on their other plans of the last 7 days, opens every plan of a statement with the one in use marked, and needs no Diagnostics Pack for it. On databases licensed for both the Diagnostics and the Tuning Pack, the automatic plan fix of Scops sets a statement that is slower than its best recent plan back to that plan with a SQL profile, in dry run first, and rolls back a fix that does not help.

Questions

Do I need the Diagnostics Pack to see the old plan?
Not while the old cursor is in the shared pool: V$SQL and V$SQL_PLAN show both. Once it is gone, the history is in DBA_HIST views, which need the pack, or in a copy you kept yourself.

Why did the plan change when nothing changed?
Statistics were gathered, a bind value was peeked, an adaptive feature saw real row counts, or the data crossed a threshold. The LAST_ANALYZED column and the IS_BIND_SENSITIVE and IS_REOPTIMIZABLE flags in V$SQL say which.

Is a SQL plan baseline a hint?
No. It limits the optimizer to the plans you accepted for that statement. New plans are captured but not used until they are evolved, so the statement stays stable while the data changes.

How long does Oracle keep old statistics?
31 days by default. DBMS_STATS.GET_STATS_HISTORY_RETENTION shows the value, and DBA_TAB_STATS_HISTORY lists the versions you can restore.

Sources

Oracle: V$SQL, DBMS_SPM, DBMS_STATS, DBMS_XPLAN
Oracle: Managing SQL Plan Baselines
Scops docs: Insights, Automatic plan fix, Detector reference