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.

Same loop as reading a plan, one level deeper: find the estimate that's wrong, then match the fix to the reason it's wrong. A re-gather is one branch, not the whole tree.

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.

Frequently asked questions

What are optimizer statistics in Oracle?

Optimizer statistics are stored descriptions of your data that the cost-based optimizer uses to estimate how many rows each step of a query will process and how expensive each access path is. They include table statistics (row count, block count, average row length), column statistics (number of distinct values, high and low values, nulls, density), index statistics (height, leaf blocks, clustering factor), and histograms (the distribution of values in a skewed column). The optimizer combines these into a cardinality estimate; if the statistics are wrong or stale, the estimate is wrong and the plan can be poor even though the optimizer reasoned correctly.

How often should I gather optimizer statistics in Oracle?

For most tables, let the automatic optimizer statistics task do it. It runs in the maintenance window and gathers statistics only for objects whose statistics have gone stale (roughly 10% of rows changed), rather than re-gathering everything. From 19c, high-frequency automatic statistics can check for stale objects every 15 minutes, and real-time statistics maintain basic stats during DML. Gather manually only in specific cases the automation cannot handle well: right after a bulk load, on a volatile or staging table (where you usually lock stats instead), or when you need to create extended statistics. Blanket nightly re-gathers of unchanged tables waste resources without improving plans.

What are extended statistics and when do I need them?

Extended statistics are statistics on a group of columns or on an expression, rather than a single column. You need column group statistics when two or more columns are correlated and are used together in WHERE clauses — for example make and model, or country and city. By default the optimizer assumes columns are independent and multiplies their selectivities, which badly underestimates cardinality when the columns are related. A column group gives the optimizer a real number of distinct values for the combination. Expression statistics do the same for a function such as UPPER(last_name). Create them with DBMS_STATS, for example method_opt of FOR COLUMNS (make, model).

Why does Oracle underestimate rows when I filter on two related columns?

Because by default the optimizer treats predicates on different columns as independent and multiplies their selectivities. If you filter on make = TOYOTA and model = CAMRY, it multiplies the selectivity of make by the selectivity of model — but every Camry is already a Toyota, so the make predicate adds no additional filtering. Multiplying by it anyway divides the estimate by the number of makes for no reason, producing a large underestimate. The optimizer then picks a plan suited to far fewer rows than actually match, such as an index range scan and nested loop where a full scan and hash join were correct. Extended statistics on the column group fix the estimate.

What is the difference between a histogram and extended statistics?

A histogram describes the distribution of values within a single column, so the optimizer knows that some values are far more common than others rather than assuming an even spread. You need one when a single column is skewed and appears in predicates. Extended statistics describe a relationship the optimizer cannot see from single-column stats: either a correlation between multiple columns (a column group) or the selectivity of an expression. They solve different problems — a histogram fixes a per-value skew underestimate or overestimate on one column, while a column group fixes the underestimate caused by treating correlated columns as independent. Some queries need both.

Should I change the DBMS_STATS default parameters?

Usually not. The defaults — AUTO_SAMPLE_SIZE for the sample size and FOR ALL COLUMNS SIZE AUTO for method_opt — are the result of extensive tuning by the optimizer development team. AUTO_SAMPLE_SIZE gives near-full-scan accuracy at a fraction of the cost, and SIZE AUTO creates histograms only for columns that are skewed and actually used in predicates. Overriding them with a fixed estimate_percent or a blanket histogram size such as SIZE 254 typically makes gathering slower and the resulting statistics less accurate. Change preferences deliberately and per-table when you have a specific reason, not as a global habit.

What are pending statistics and how do I use them?

Pending statistics let you gather new statistics without immediately exposing them to the optimizer, so you can test them before they affect everyone. Set the table preference PUBLISH to FALSE, gather statistics (they are stored as pending, and other sessions keep using the old published stats), then in your own session set optimizer_use_pending_statistics to TRUE and run your critical queries to check the plans. If the new statistics produce good plans, publish them with DBMS_STATS.PUBLISH_PENDING_STATS; if not, delete them and no other session was ever affected. This is the safe way to re-gather statistics on an important table where a plan regression would be costly.

Why should I lock statistics on some tables?

You lock statistics on tables whose contents change so dramatically and so often that any automatically gathered snapshot is misleading — typically staging, load, or global temporary tables that are empty for most of the day and full for a few minutes. If the automatic statistics job happens to run while such a table is empty or half-loaded, the optimizer gets statistics describing a state the table is almost never in, and plans that reference it go wrong. Instead you gather representative statistics once, when the table holds a typical working set, and lock them with DBMS_STATS.LOCK_TABLE_STATS so the automatic job leaves them alone.

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. And once the number is right and the plan is good, SQL Plan Management is how you keep it that way — pinning the plan so the next re-gather can't quietly undo the fix.

Originally published at uptimearchitect.com.