EXPLAIN PLAN is a liar. Not on purpose — it just tells you what the optimizer hopes will happen, never what did. It's a forecast, printed with the confidence of a receipt.
And a plan is nothing but forecasts stacked on forecasts. The optimizer guesses how many rows each step will produce, and every choice after that — which table to lead with, whether to use an index, nested loop or hash join — rests on the guess before it. Get the first estimate wrong and the whole plan tips over: it thought a step would return five rows, it returned five million, and it picked a strategy that's a catastrophe at that scale. That's what a slow query almost always is. Not a mysterious optimizer mood — one estimate that missed, and a plan that trusted it.
So here's the part that changes how you tune: reading the estimates is nearly useless, because the estimate is exactly the thing that's wrong. The skill is reading the estimate against reality — putting the number the optimizer guessed next to the number it actually got, and finding the line where they diverge. Do that and the slow query stops being a mystery. Everything below is how.
Stop reading the guess. Get the real plan.
EXPLAIN PLAN, AUTOTRACE, the plan tab in your IDE — they all show you the estimate without ever running the statement. Worse, the plan they show you isn't guaranteed to be the plan that runs: bind variables get peeked, the real cursor may have been built for a different value, and EXPLAIN PLAN doesn't peek at all. You can spend an afternoon tuning a plan the database never actually uses.
Run the statement and ask the database what it actually did. Two pieces: the GATHER_PLAN_STATISTICS hint (or STATISTICS_LEVEL = ALL for the session), which tells Oracle to count real rows as it goes, and DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST format, which prints those counts next to the estimates for the statement you just ran:
SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(amount)
FROM shop.orders
WHERE status = 'OPEN';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));
That gives you a plan with columns the estimate-only tools can't:
- E-Rows — rows the optimizer estimated a step would return.
- A-Rows — rows it actually returned.
- Starts — how many times the step ran (crucial for nested loops).
- Buffers — logical I/O, the truest measure of work done (more on this below).
This is the difference between a weather forecast and looking out the window. Everything that follows is reading that window.
The one number: E-Rows vs A-Rows
If you learn to read exactly one thing in a plan, read this. Go down the plan and compare E-Rows to A-Rows on every line. Where they track each other, the optimizer understood the data. Where they diverge by an order of magnitude or more, you've found the lie — and it's almost always the root of the slowness, because every operation above that line was planned for the wrong number of rows.
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 8616 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 8616 |
|* 2 | TABLE ACCESS FULL| ORDERS | 1 | 500K| 500 |00:00:00.02 | 8616 |
------------------------------------------------------------------------------------
Line 2 is the whole story. The optimizer estimated 500K rows for status = 'OPEN' and got 500 — a thousandfold overshoot. Believing half the table matched, it did the sensible thing for that belief and full-scanned. But only 500 rows matched, so it read over eight thousand buffers to find a few hundred rows an index would have fetched in ten. The full scan isn't the bug. The 500K estimate is the bug; the full scan is just what a rational optimizer does when you feed it a bad number.
For nested loops, apply the same idea with Starts: the inner step's real output is Starts × A-Rows. A step showing Starts = 50000 is an inner probe that ran fifty thousand times because the optimizer thought the outer row source would return a handful. Same disease — a low estimate on the driver — different symptom.
Read it inside-out
Plans read like nested parentheses, not top to bottom. The most-indented line runs first; a parent consumes what its children produce. To find where a plan goes wrong, start at the leaves (the table and index accesses), walk outward, and watch two columns: the A-Rows that balloon and the Buffers that accumulate. The line where the row count first explodes past its estimate is your driving row source — the step that set the plan's fate. You don't need to understand all forty lines of a hairy plan. You need the one where reality parted ways with the forecast.
When a full scan is right — and when it's a symptom
A full table scan is not a failure. Reading 40% of a table, it's faster than an index — an index would mean 400,000 scattered single-block reads where a scan does clean multi-block ones. The optimizer switches between index access and full scan on selectivity: how much of the table your predicate keeps. Below roughly a few percent, the index wins; above it, the scan does.
Which means a full scan is only a symptom when the optimizer reached for it because it misjudged selectivity — the exact case above, where a missing histogram made a 0.05%-selective predicate look 50%-selective. So don't reflexively "add an index" or paste in an INDEX hint. A hint that forces the index papers over the wrong estimate and leaves a landmine for the next value. Fix the number, and the optimizer picks the index on its own — for every value, not just the one you tested.
While you're on that line, read its predicate section (DISPLAY_CURSOR prints it under the plan). It splits into access predicates — what an index used to find rows — and filter predicates — what got applied after, throwing rows away. A fat filter where you expected an access predicate means the index isn't doing the work you think it is: rows are being read and then discarded, not skipped.
Buffers, not seconds
The instinct is to chase A-Time. Resist it. Elapsed time lies — it drops when data's cached, spikes when the box is busy, and changes every run. Buffers — logical I/O, the count of buffer accesses — is the work the query actually asked for, and it's stable across warm cache, cold cache, and a loaded server. When you tune, watch Buffers fall. A rewrite that "feels faster" but moves the same Buffers just cached the blocks; a rewrite that cuts Buffers 100× is genuinely less work, and it'll still be less work at 2am under load. Time is the thing users feel; Buffers is the thing you can trust while you're fixing it.
Why the estimate was wrong
Once E-Rows and A-Rows point you at the guilty line, there's one question left: why did the optimizer believe that? Almost always one of these:
-
Stale or missing statistics. The optimizer is reasoning about a table that no longer exists — last week's row counts, yesterday's high value. Re-gather with
DBMS_STATSand the estimate often just fixes itself. -
Column skew with no histogram. The classic. The optimizer assumes values are spread evenly across a column, so for
status = 'OPEN'it divides row count by the number of distinct values. IfOPENis 0.05% of the table but there are two distinct values, it estimates 50%. A histogram on the column tells it the truth about the distribution. (This is the worked example below.) -
Correlated columns. Two predicates the optimizer treats as independent when they aren't —
WHERE make = 'Toyota' AND model = 'Camry'. It multiplies the two selectivities and lands far too low, because every Camry is a Toyota. Extended statistics on the column group teach it the correlation. -
A function on the column.
WHERE UPPER(name) = 'ACME'or an implicit type conversion (a number column compared to a string) makes the column unsargable — the index can't be used and the estimate falls apart. Rewrite to leave the column bare, or add a function-based index. - Bind peeking meets skew. With bind variables, the optimizer peeks at the first value and builds a plan for it — great until the next value has wildly different selectivity and inherits a plan built for someone else. Adaptive cursor sharing exists to catch this; skew plus binds is where it earns its keep.
- A predicate past the edge of the stats. Query for a date newer than the newest value the stats know about and the optimizer estimates almost nothing matched — so it under-reads. Common on ever-growing tables between stats gathers.
Modern Oracle fights back on your behalf: adaptive plans (12c and up) let the optimizer switch join methods mid-flight when the real row counts contradict the estimate, and Real-Time SQL Plan Management in 23ai spots a plan regression as it happens and reinstates a known-good plan automatically. Both are real, and both are worth having on. But they're a safety net under the trapeze, not a reason to skip the routine: they catch some falls, they don't make your statistics tell the truth. The durable fix is still to correct the number the optimizer started from.
Watch it happen
Take the plan from earlier and give the optimizer what it was missing. Before, with no histogram on status, it split the table evenly between two values and estimated 500K rows for OPEN — so it full-scanned. Gather a histogram:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SHOP',
tabname => 'ORDERS',
method_opt => 'FOR COLUMNS SIZE 254 STATUS'); -- build the histogram it was missing
END;
/
Run the same query again, pull the same ALLSTATS LAST plan, and the guilty line has changed its mind:
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 10 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 10 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS | 1 | 500 | 500 |00:00:00.01 | 10 |
|* 3 | INDEX RANGE SCAN | ORD_STATUS_IX | 1 | 500 | 500 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------
Now E-Rows is 500 and A-Rows is 500. The estimate matches reality, so the optimizer reaches for the index on its own — no hint, no forcing — and Buffers collapse from 8,616 to 10. (The elapsed times barely move, because a million cached rows scan fast — which is exactly why you read Buffers, not the clock. On a table too big to sit in memory, that Buffers gap is the difference between milliseconds and minutes.) You didn't tune the query. You corrected the one number it was wrong about, and the right plan fell out.
One loop, not a hundred knobs: get the real plan, find the line where estimate and reality part ways, fix the reason the estimate was wrong — not the symptom the optimizer picked because of it.
Don't take my word for it — run it. The execution-plans lab stands up an Oracle Database Free container, builds a million-row table where
OPENis deliberately rare, and gathers stats without a histogram. It runs the query, captures the realALLSTATS LASTplan, and asserts the misestimate is there — a full scan with E-Rows a thousandfold over A-Rows. Then it gathers the histogram, re-runs, and asserts the plan flipped to an index range scan with E-Rows ≈ A-Rows. If the misestimate doesn't reproduce, or the fix doesn't correct it, the run fails. The whole before/after is proven on every CI push, not asserted in prose.
What teams get wrong
-
Tuning the estimate-only plan.
EXPLAIN PLANshows a guess and sometimes not even the guess that runs. Tune the plan the database actually executed —ALLSTATS LAST— or you're tuning fiction. - Reading E-Rows and stopping. The estimate is the suspect, not the evidence. Its value is only in the gap between it and A-Rows.
- Forcing an index with a hint. A hint fixes today's value and hides the real defect — a broken estimate — until the next value walks into the same trap. Fix the stats; let the optimizer choose.
- Chasing seconds. Elapsed time moves with cache and load. Chase Buffers; that's the work that doesn't lie between runs.
- Blaming the optimizer. It's not moody. Given honest numbers it makes good choices; given a skewed column with no histogram it makes a bad one for a good reason. Feed it the truth.
- Gathering stats blindly and hoping. A plain re-gather fixes stale numbers but not skew, not correlation, not a function on a column. Read why the estimate was wrong, then pick the matching fix.
Frequently asked questions
What is the difference between EXPLAIN PLAN and DBMS_XPLAN.DISPLAY_CURSOR?
EXPLAIN PLAN produces the optimizer's estimated plan without executing the statement, and because it does not peek at bind variables it can even show a different plan than the one that actually runs. DBMS_XPLAN.DISPLAY_CURSOR shows the plan for a cursor that really executed, and with the ALLSTATS LAST format it prints actual row counts (A-Rows), start counts, and logical I/O (Buffers) next to the optimizer's estimates (E-Rows). For tuning you want DISPLAY_CURSOR with real execution statistics, because the estimates alone are exactly what tends to be wrong.
How do I get actual row counts (A-Rows) in an Oracle execution plan?
Either add the /*+ GATHER_PLAN_STATISTICS */ hint to the query, or set STATISTICS_LEVEL = ALL for the session, so Oracle counts real rows as the statement runs. Then display the plan with SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST')). The output adds A-Rows (actual rows per step), Starts (how many times each step ran), and Buffers (logical I/O) alongside the estimated E-Rows, so you can compare what the optimizer predicted against what actually happened.
What does it mean when E-Rows and A-Rows are very different?
It means the optimizer's cardinality estimate for that step was wrong, and that is usually the root cause of a bad plan. The optimizer chooses join methods, join order, and access paths based on how many rows it expects each step to produce; if it expects five rows and gets five million (or the reverse), every decision above that line was made for the wrong scale. Find the plan line where E-Rows and A-Rows diverge by an order of magnitude or more, and you have found the estimate to fix.
Why is Oracle choosing a full table scan instead of my index?
Usually because it estimates your predicate matches a large fraction of the table, which would make a full scan genuinely faster than many single-block index reads. If the estimate is correct, the full scan is the right choice. If the estimate is wrong — often because a skewed column has no histogram, so the optimizer assumes even distribution — fix the estimate rather than forcing the index with a hint. A histogram, fresh statistics, or extended statistics will let the optimizer pick the index on its own for every value, not just the one you tested.
What are E-Rows, A-Rows, Starts, and Buffers in DISPLAY_CURSOR output?
E-Rows is the estimated number of rows the optimizer expected a step to return. A-Rows is the actual number it returned at run time. Starts is how many times that step executed — important for nested loops, where the inner step runs once per outer row, so its true output is Starts multiplied by A-Rows. Buffers is logical I/O, the number of buffer accesses the step performed, which is the most reliable measure of work because it does not vary with caching or server load the way elapsed time does.
Should I use a hint to fix a bad execution plan?
Usually not as the first move. A hint that forces an index or a join method fixes the specific case in front of you but masks the underlying cause — typically a wrong cardinality estimate — and can produce a worse plan for a different bind value or as the data grows. Diagnose why the estimate is wrong (stale statistics, missing histogram, correlated columns, a function on a column) and correct that. Then the optimizer makes the right choice for all values. Hints are a last resort or a temporary stabilizer, not the fix.
What is a histogram and when do I need one?
A histogram is a statistic that describes how values are distributed within a column, rather than assuming they are spread evenly. You need one when a column is skewed — a few values appear far more or far less often than the rest — and it is used in WHERE-clause predicates. Without a histogram the optimizer divides the row count by the number of distinct values, which badly misestimates selectivity for rare or dominant values. Gather one with DBMS_STATS.GATHER_TABLE_STATS using METHOD_OPT such as FOR COLUMNS SIZE 254 column_name.
Do adaptive plans and Real-Time SPM mean I can stop tuning statistics?
No. Adaptive plans (from 12c) can switch join methods at run time when actual row counts contradict the estimate, and Real-Time SQL Plan Management in 23ai can detect a plan regression as it happens and automatically reinstate a known-good plan. Both reduce the blast radius of a bad estimate, but neither makes your statistics accurate — they react to problems rather than prevent them. Correct statistics still produce better first plans across the whole workload; the adaptive features are a safety net beneath that, not a replacement for it.
Reading a plan is the third skill in the same performance discipline as the other two: an AWR report tells you which SQL is expensive across the whole database, wait events tell you what a session is stuck waiting on, and the execution plan tells you why a single statement is doing too much work. Start at the top, narrow to the statement, then open the plan and find the one line where the estimate and reality part ways. Prove the whole loop end to end with the execution-plans lab — a misestimate you can watch reproduce, and a histogram you can watch fix it.
Originally published at uptimearchitect.com.
Top comments (0)