A query that ran in 400 milliseconds for six months suddenly runs in 12 seconds. The application team escalates. The DBA on call opens AWR. This happens more than most people admit, and the pattern is the same every time.
The plan hash flipped. That much is obvious in AWR within two minutes. The question is why, and the standard diagnosis path stops most engineers cold.
What the evidence shows and does not show
Object statistics are fresh. Last analyzed is recent, stale_stats is FALSE, and modifications_since_stats is well below the threshold that would justify a re-gather. There is no DDL in the recent changes window on any object referenced by the plan. The predicates in the baseline plan and the current plan reference the same objects with the same access paths conceptually. Nothing was dropped, nothing was added, nothing was rebuilt. On paper, nothing changed.
This is where the standard diagnosis path stops. Most engineers regather statistics out of habit, hope the plan flips back, and file a ticket with Oracle Support if it does not. That instinct is understandable and wrong.
The absence of a structural cause is the diagnosis
Ruling out stale statistics, dropped indexes, and DDL is not ambiguity. It is evidence. What remains after eliminating structural causes is optimizer plan instability. The optimizer chose a different plan and it happened to be worse. The prior plan was known good because it ran production for six months at 400 milliseconds per execution. That is a real finding, not a shrug.
A senior Oracle DBA reads this differently than a runbook does. A runbook says “if plan changed, gather statistics.” A senior DBA says “if plan changed and there is no structural cause, the optimizer is the cause, and the prior plan is the fix.”
Why a SQL plan baseline beats a stats refresh here
The correct move on a Class A regression with a known good prior plan is DBMS_SPM. Pin the prior plan via a SQL plan baseline. Restore performance in the next execution of the statement. Investigate why the optimizer flipped afterward, on your schedule, not under an escalation.
A SQL plan baseline is fully reversible. Drop the baseline and the optimizer returns to its current behavior with zero residual effect. That property is what makes it Class A. Reversibility discipline is what separates a fix a senior DBA will reach for in an emergency from a fix that creates a second problem to solve later.
Compare briefly to a stats refresh. Refreshing statistics on a large fact table on a 150TB data warehouse takes real time, generates real load, and might produce a third plan that is worse than either the baseline or the current one. Now you own two problems: the original regression and the new plan the fresh statistics generated. Undoing that is not straightforward. A stats refresh is Class B for a reason.
Compare also to a SQL profile. Profiles work but they depend on Tuning Advisor, which reopens Diagnostic and Tuning Pack licensing exposure. A SQL plan baseline hard-pins the specific plan without that dependency. On a licensed environment where every pack usage counts, this matters.
What is actually causing plan flips on large data warehouses
On an ExaCC RAC environment running a 150TB data warehouse, this pattern shows up around quarter-end almost every quarter. The mechanism is adaptive cursor sharing responding to a bind variable distribution the optimizer has not seen since the last comparable workload spike. Quarter-end reporting joins hit fact tables in cardinalities that look nothing like the daily workload profile. The optimizer sees an unfamiliar distribution, decides the prior plan is no longer optimal, and generates a new plan that might be worse for the actual data.
The optimizer is doing what it was designed to do. Adaptive cursor sharing exists specifically to handle this case. The problem is that on a specific query with a specific baseline that was performing well, the optimizer’s decision to try a different plan is the regression. Pinning the prior plan takes the decision out of the optimizer’s hands for that statement.
Some DBAs will argue this is fighting the optimizer. I disagree. This is respecting the evidence. A plan that ran at 400 milliseconds for six months on a real workload is a stronger signal than a plan the optimizer chose in the last five minutes based on a bind distribution shift.
The mental model
Every regression gets triaged against reversibility, not just fix time. Class A is fully reversible with zero residual effect: SQL plan baseline pin, session-level parameter change. Class B is reversible with care: statistics refresh (might generate a different plan), index recreation (might reintroduce a problem someone was solving). Class C is heavier structural change: table restructure, partition strategy change, application code change.
Emergency situations reach for Class A first, always. Post-incident investigations decide whether Class B or C is warranted based on root cause, not urgency. A baseline pin that resolves an emergency does not become permanent by accident. It becomes an interim mitigation while you decide whether the underlying plan flip warrants a durable fix or is a quarterly artifact you can live with.
This is the discipline a senior Oracle DBA brings to a regression that a documentation reader does not. Reversibility calculus. Evidence discipline. The recognition that ruling out structural causes is a diagnosis, not ambiguity.
The larger point
Most Oracle performance work in mid-market shops runs on runbook-driven habit. Regather stats, restart the service, escalate to Oracle Support. That path works often enough to be defensible and stops short of the reasoning that would actually resolve the underlying pattern.
Twenty years of production Oracle including Exadata X5 through X11 depth teaches you to read what the evidence rules out as carefully as what it confirms. A plan flip with fresh statistics, no DDL, and no structural change is not a mystery. It is a positive diagnosis of optimizer plan instability with a reversible fix that any senior DBA should reach for.
If your team is triaging regressions against fix time rather than reversibility, that is worth a conversation. I run 30-minute discovery calls at no charge to walk through what your current escalation path looks like and where the reasoning gaps are. Details at sitratec.com.
