Query plan regressed (suddenly slow)

A query that was fast yesterday is suddenly slow today. Usually the optimizer has switched to a worse plan. We find the root cause among stale statistics, parameter sensitivity, data volume growth or a sudden plan change, and stabilize the plan.

Did the plan really change, and when?

Are there multiple plans (plan_hash/plan_generation) for the same query, and when did the slowdown start? Does it coincide with a recent deploy, upgrade, parameter change or large data load?

SELECT sql_id, plan_hash_value, executions, ROUND(elapsed_time/GREATEST(executions,1)/1e6,3) AS sec_per_exec, first_load_time
FROM v$sql WHERE sql_id='&sqlid' ORDER BY first_load_time;

• Statistics may be stale
• The same query behaves differently depending on the parameter (sniffing/peeking)
• Data volume has grown significantly
• The plan changed suddenly after an upgrade/flush

Are the statistics current?

The optimizer bases its decisions on statistics. If the last analyze date is old or the modification counter is high, the estimates may really have drifted, which leads to a bad plan.

SELECT table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics WHERE table_name='T';

• Statistics are stale/missing
• Statistics are current but the plan is still parameter-sensitive

Is there parameter sensitivity?

The query chooses and caches a plan based on the parameter value of its first execution (MSSQL parameter sniffing / Oracle bind peeking). That plan can be bad for other values with a skewed distribution.

SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_id='&sqlid';

• Yes, the plan flips between good and bad depending on the parameter
• The right plan is known, I want to pin it permanently

Root cause: stale / missing statistics

Based on old statistics that do not match reality, the optimizer misestimated the row count and chose a bad plan. This is the most common cause of plan regression.

Root cause: parameter sensitivity (sniffing / bind peeking)

The cached plan was chosen for the first parameter value that arrived and is bad for other values with a skewed distribution. The goal is to make sure a plan suited to the value is chosen.

Solution: pin the right plan (baseline / force plan)

If a known good plan exists, pinning it so the optimizer does not deviate from it stops the regression permanently.

Root cause: data volume grew, the plan no longer fits

As the table grows, a plan that used to be ideal (e.g. nested loop) now scales poorly; the optimizer needs statistics and access paths that correctly reflect the growth.

Root cause: sudden plan change (upgrade / cache flush)

The plan changed suddenly after a version upgrade, a parameter change or the plan cache being emptied. The optimizer behavior or environmental conditions have shifted.