A query that finished in under a second last week is now timing out. Nobody changed the SQL. The first question is not “what’s wrong with the database” — it’s “what changed in the plan.” That’s a diff question, and it’s a fast way into the problem if you have something to diff against.
OraTune’s Execution Plans tab exists for exactly this. Point it at a baseline DBMS_XPLAN output and a current one — file uploads or a Live DB explain — and it builds a plan-tree comparison with cost and cardinality shown per node. Underneath, the comparison engine tracks a specific set of deltas: which operations flipped from an index scan to a full table scan, which indexes appeared, disappeared, or changed access method, any join method changes (nested loops to hash join, for example), new sort operations that weren’t there before, and whether the plan shape changed outright. It also computes the baseline total cost, the current total cost, and the cost delta as a percentage, so you’re not eyeballing two plan trees trying to spot the divergence by hand.
As an illustration of what that side-by-side view surfaces — this is a constructed example, not a real captured run — a baseline plan against an ORDERS table might read:
BASELINE
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 42 |
| 1 | TABLE ACCESS BY INDEX ROWID | ORDERS | 310 | 42 |
| 2 | INDEX RANGE SCAN | ORDERS_CUST_IX | 310 | 6 |
--------------------------------------------------------------
CURRENT
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 8410 |
| 1 | TABLE ACCESS FULL | ORDERS | 41200| 8410|
--------------------------------------------------------------
That’s the shape a full_scan_regressions hit looks like: an index access path replaced by a full scan, the row estimate off by two orders of magnitude, and cost following it up. In OraTune this shows up as a flagged operation change in the plan comparison, not just a bigger number at the bottom.
Once the tab has pointed at the specific operation, the diagnosis narrows fast. In order of how often each one turns out to be the cause:
Stale statistics. The optimizer estimated 310 rows and got 41,200. If ORDERS has grown or its data distribution has shifted since the last DBMS_STATS gather, the cardinality estimate feeding the whole plan is wrong before the optimizer even picks an access path. OraTune’s Stats Health check (in Live DB mode) flags exactly this condition — statistics marked stale, or a last_analyzed date older than 30 days.
A changed bind value or predicate selectivity. If the query is parsed with a value that now matches a much larger slice of the table, a plan that was correct for a selective bind stops being correct once the data behind that bind shifts. This won’t show up as a stats problem; it shows up as the optimizer making a locally reasonable choice for input it wasn’t tuned for.
A dropped or invisible index. If ORDERS_CUST_IX was dropped, marked invisible, or rebuilt with different columns, there’s no range scan left to choose. This is a straightforward read off the plan comparison’s index-changes list — an index that existed in the baseline plan and is simply absent from the current one.
A clustering-factor shift. Even with the index still in place, a big jump in clustering factor makes the optimizer’s index-scan cost estimate look worse than a full scan, so it switches paths on its own. This is common after a bulk load or reorg changes physical row order relative to the indexed column.
The fix follows directly from which of these it is: gather fresh statistics on the affected table (and any histogram-bearing columns), confirm the index still exists and is visible, or investigate the clustering factor directly against DBA_INDEXES. If the regression is time-sensitive and the root cause needs more investigation, a SQL Plan Baseline pinning the known-good plan buys time without guessing at a permanent fix — OraTune’s Plan Baselines tab lists, enables, and promotes baselines via DBMS_SPM directly against the connected database.
Whichever cause it turns out to be, the workflow is the same: diff the plans, let the comparison point at the specific operation that changed, and confirm the fix by re-running the diff and watching the cost delta close instead of just watching the query finish once.