Oracle Optimizer Statistics, Demystified

The optimizer doesn’t guess. It calculates — and every number in that calculation comes from statistics you either gathered on purpose or left to rot.

That’s the part the “the optimizer is dumb” stories always skip. Reading an execution plan, you find the line where the estimate parted ways with reality — the step that expected five rows and got five million, and dragged the whole plan down with it. That’s not the optimizer being stupid. That’s the optimizer doing correct arithmetic on a wrong number. It thought five rows because the statistics said five rows. Fix the plan and you’re treating a symptom. Fix the statistics and the bad plan never gets built.

So here’s the reframe: tuning statistics isn’t a nightly ritual of gathering everything and hoping. Most of that is cargo cult — full re-gathers that burn a maintenance window to re-confirm numbers that hadn’t moved. The actual skill is narrow. Gather the right things — a histogram where a column is skewed, an extended statistic where columns are correlated — keep them fresh where the data actually moves, and let the automatic job handle the rest instead of fighting it. Everything below is which numbers matter and how to keep them honest.

What the optimizer is actually reading

When the optimizer estimates how many rows a step returns — its cardinality — it’s doing one sum: rows × selectivity. The row count and the selectivity both come from stored statistics, gathered by DBMS_STATS and kept in the data dictionary:

• Table statistics — number of rows, number of blocks, average row length. This is the rows in the sum.
• Column statistics — for each column: number of distinct values (NDV), low and high value, number of nulls, and density. This is where basic selectivity comes from: for col = :x, the optimizer assumes 1/NDV of the rows match.
• Index statistics — height, leaf blocks, clustering factor (how well index order matches table order). This decides whether an index access is actually cheaper than a scan.
• Histograms — the shape of a column’s data when it isn’t evenly distributed. Without one, the optimizer assumes every value is equally common. (More below.)

Get those right and the optimizer makes good choices. Feed it a stale row count, a missing histogram, or two columns it thinks are independent, and it makes a bad one — confidently, because the math checks out.

Histograms: when “1/NDV” lies

The 1/NDV assumption falls apart the moment a column is skewed — a few values far more common than the rest. If status is 99% CLOSED and 0.1% OPEN, but the optimizer only knows there are two distinct values, it estimates half the table for either one. A histogram records the real distribution so the optimizer stops assuming and starts knowing. You need one when a column is skewed and shows up in WHERE clauses — and Oracle’s default METHOD_OPT of FOR ALL COLUMNS SIZE AUTO creates them only for exactly those columns, using its record of which columns get queried.

This is the single most common estimate bug, and it’s the one the execution-plans lab reproduces end to end — a missing histogram turning a 500-row query into a full-table scan. So I won’t relitigate it here. The second most common bug gets far less airtime and is just as destructive:

Correlated columns: the estimate that quietly collapses

Give the optimizer two predicates on the same table and, by default, it assumes the columns are independent — it multiplies their selectivities together. WHERE make = 'TOYOTA' AND model = 'CAMRY' becomes selectivity(make) × selectivity(model). That’s fine when the columns really are unrelated. It’s a disaster when they aren’t — because every Camry is a Toyota. The model predicate already implies the make; multiplying by selectivity(make) a second time divides the estimate by the number of makes for no reason at all.

The result is a severe under-estimate — the mirror image of the skew problem. The optimizer thinks a few dozen rows match when tens of thousands do, so it reaches for an index and a nested loop that would be perfect for a few dozen rows and catastrophic for tens of thousands. You can’t fix this with a histogram; neither column is individually skewed. You fix it by telling the optimizer the two columns travel together — an extended statistic on the column group:

-- create the column group AND gather it in one step
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SALES',
    tabname    => 'CARS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS (make, model)');
END;
/

-- confirm the extension exists
SELECT extension_name, extension
FROM   user_stat_extensions
WHERE  table_name = 'CARS';

Now the optimizer stores a real NDV for the combination of make and model, and the estimate snaps to the truth. Extended statistics also cover expressions — if you query WHERE UPPER(last_name) = 'SMITH', a statistic on (UPPER(last_name)) gives the optimizer a real selectivity for the expression instead of a blind guess. (There’s an automation for this too: the preference AUTO_STAT_EXTENSIONS, off by default, lets Oracle create column groups on its own from the predicates it sees. Useful, but I like knowing exactly which extensions exist rather than discovering them.)

The estimate that rots: stale, and out of range

The other way good statistics go bad is simply time. Stats are a snapshot; the data keeps moving. Two symptoms matter most:

• Stale stats on a changing table. Oracle flags a table’s stats stale once roughly 10% of its rows have changed since the last gather. Past that line, the optimizer is reasoning about a table that no longer exists.
• The out-of-range predicate. This one’s sneakier. Gather stats today, and the optimizer’s high_value for order_date is today. Query tomorrow for order_date > SYSDATE - 1 and you’re asking about values past the edge of what the stats know — so the optimizer estimates almost nothing matched and under-reads. On any ever-growing table (orders, events, logs), the newest and most-queried data is exactly the data the last gather never saw.

Both are why you don’t just gather once and forget. But they’re also why you shouldn’t gather everything nightly — most tables don’t cross the staleness line most nights. Which is what the automatic job is for.

Let the automatic job do the boring part

Modern Oracle gathers statistics for you, and it’s good at it. The automatic optimizer statistics task runs in the maintenance window and gathers stats for any object whose stats have gone stale — not every object, just the ones that moved. Two newer pieces make it sharper, both from 19c on:

• High-frequency automatic statistics — a lightweight task that checks for stale objects every 15 minutes rather than waiting for the nightly window, so fast-moving tables don’t spend all day on yesterday’s numbers.
• Real-time statistics — the optimizer maintains basic stats as conventional DML runs, so a table loaded this morning isn’t invisible until tonight’s gather. (Availability varies by edition and platform — know whether yours has it.)

Your job is mostly to not get in the way. The DBMS_STATS defaults are the product of two decades of the optimizer team’s scar tissue — AUTO_SAMPLE_SIZE (a fast, accurate scan rather than a guessed sample percent) and SIZE AUTO (histograms only where they’re earned). Overriding them with a hand-picked estimate_percent or a blanket SIZE 254 is how people make stats worse while feeling productive. Leave the defaults, and reach for manual gathering only in the specific cases the automation can’t see:

-- a good manual gather looks like the defaults, on purpose
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SALES',
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE);
END;
/

Two overrides that are worth knowing. Lock the stats on a volatile or staging table so the automatic job doesn’t gather it mid-load and catch it half-empty — you set representative stats once and freeze them:

EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES', 'STAGING_LOAD');

And when you must re-gather a critical table but can’t risk a plan regression, use pending statistics — gather them, test them in your own session, and publish only if they behave:

EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');   -- lands as PENDING, unused by other sessions
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;  -- test the new stats, just for you
-- happy? publish. unhappy? delete them, no one else ever saw them.
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES', 'ORDERS');

Is it actually your statistics?

Before you gather anything, confirm the stats are the problem. Two checks:

-- how old, how big, and does Oracle think they're stale?
SELECT table_name, num_rows, last_analyzed, stale_stats
FROM   user_tab_statistics
WHERE  table_name = 'ORDERS';

Then the same check that reading any plan comes down to: run the statement with /*+ GATHER_PLAN_STATISTICS */, pull the plan with DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'), and compare E-Rows to A-Rows. If they track, your stats are fine and the work is genuinely large — a re-gather won’t help. If they diverge by an order of magnitude, you’ve found the estimate to fix, and this post is the menu of why it’s wrong. Reading that plan is the subject of its own post (Oracle Execution Plans, Decoded); statistics are where the fix usually lands.

flowchart TD
A["Plan is slow"] --> B["ALLSTATS LAST:<br/>compare E-Rows to A-Rows"]
B --> C{"Do they diverge<br/>by an order of magnitude?"}
C -- No --> D["Stats are fine —<br/>the work is real. Reduce it."]
C -- Yes --> E{"Why is the estimate wrong?"}
E --> F["One skewed column<br/>-> histogram (SIZE AUTO)"]
E --> G["Correlated columns / expression<br/>-> extended stats"]
E --> H["Stale or out-of-range<br/>-> re-gather (defaults)"]
E --> I["Volatile / staging table<br/>-> lock + set manually"]
F --> J["Re-check: E-Rows now<br/>tracks A-Rows"]
G --> J
H --> J
I --> J

Don’t take my word for it — run it. The optimizer-stats lab builds a million-row table where model determines make — perfectly correlated — and joins it to a second table. It runs WHERE make = ... AND model = ..., captures the real ALLSTATS LAST plan, and asserts the under-estimate — the optimizer multiplies the selectivities, expects ~200 rows, and picks a nested loop join that’s right for a couple hundred rows and a disaster for the ~10,000 that actually match. Then it creates an extended statistic on the (make, model) column group, re-gathers, re-runs, and asserts the plan flipped to a hash join with E-Rows ≈ A-Rows. (It turns adaptive plans off first, so you see what the estimate alone decides — the runtime safety net is a different lesson.) If the under-estimate doesn’t reproduce, or the column group doesn’t correct it, the run fails. The whole before/after is proven on every CI push.

What teams get wrong

• Gathering everything, every night. A full re-gather of tables that didn’t change is a maintenance window spent re-confirming yesterday. Let the automatic task gather what’s stale; intervene only where it can’t see.
• Overriding the defaults to feel productive. A hand-picked estimate_percent or a blanket SIZE 254 usually makes stats slower to gather and worse. AUTO_SAMPLE_SIZE and SIZE AUTO are the right answer far more often than not.
• Never creating extended statistics. The correlated-columns under-estimate is invisible until you know to look for it — and no amount of re-gathering the individual columns fixes it. A column group does.
• Deleting histograms because “they cause plan instability.” The instability is usually a bind-peeking or sampling issue, not the histogram itself. Removing a histogram a skewed column needs just trades a visible problem for a quieter one.
• Gathering stats, then not testing. On a critical table, a re-gather is a plan change. Use pending statistics to try it in one session before it becomes everyone’s plan.
• Leaving volatile and staging tables to the automatic job. Caught mid-load, they get stats that describe a table that only exists for ten minutes a day. Lock representative stats instead.

Statistics are the layer underneath every plan the optimizer builds, which is why they sit at the root of the same performance discipline as the rest: an AWR report points you at the expensive SQL, wait events tell you what a session is stuck on, the execution plan shows you where the estimate went wrong, and statistics are usually why. Don’t gather more — gather what’s earned: a histogram for skew, a column group for correlation, fresh numbers where the data actually moves, and the automatic job for everything else. Prove the correlated-columns fix end to end with the optimizer-stats lab, and the next time a plan goes wrong, you’ll know whether to read the plan or fix the number behind it.