Oracle Optimizer Statistics: How to Get DBMS_STATS Right

Almost every 'the query was fast yesterday and slow today' ticket I have chased in eighteen years traces back to one thing: optimizer statistics. The Oracle optimizer is only as good as the numbers it is given about your data, and those numbers - the statistics - are what turn a SQL statement into an execution plan. Get them right and the optimizer usually gets the plan right. Get them wrong, stale, or missing, and it guesses - badly. This guide explains what statistics actually are, how to gather and manage them properly with DBMS_STATS, and how to stop bad stats from wrecking production plans overnight.

1. What Statistics Actually Are

When Oracle parses a SQL statement, the cost-based optimizer has to decide HOW to run it: which index (if any), which join order, which join method, whether to scan the whole table or seek a few rows. It makes those choices by estimating how many rows each step will produce - and it estimates using statistics.

The core statistics are simple counts about your data: how many rows a table has, how many blocks it occupies, how many distinct values a column holds, the high and low values, and how selective each index is. From these, the optimizer calculates cardinality - the estimated row count at each step - and picks the plan it believes is cheapest.

The critical insight: the optimizer never looks at your actual data when choosing a plan. It looks only at the statistics. If the statistics say a table has 1,000 rows but it really has 10 million, the optimizer will confidently choose a plan that is catastrophically wrong - and it will be sure it is right.

2. Gathering Statistics with DBMS_STATS

The one rule that matters first: use DBMS_STATS, never the old ANALYZE command. ANALYZE still exists for backward compatibility but produces statistics the modern optimizer does not fully trust, and it lacks the features below.

-- Gather stats for one table (and its indexes), Oracle-recommended defaults
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,
    degree           => 4);
END;
/

-- Whole schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SALES', degree=>4);

Two defaults are doing heavy lifting. AUTO_SAMPLE_SIZE lets Oracle pick the sample - modern versions use a fast, accurate algorithm that reads far more than the old fixed percentages, so do not hardcode estimate_percent without a reason. METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' lets Oracle decide which columns need histograms based on how they are actually used.

3. The Automatic Statistics Job

Since 10g, Oracle runs an automatic statistics-gathering job in the maintenance window that regathers stats on objects whose data has changed significantly. For most tables, most of the time, this is enough - and turning it off is a classic self-inflicted wound.

-- Is the auto job enabled?
SELECT client_name, status FROM dba_autotask_client
WHERE  client_name = 'auto optimizer stats collection';

-- What does 'significantly changed' mean? ~10% of rows by default:
SELECT table_name, stale_stats, last_analyzed
FROM   dba_tab_statistics
WHERE  owner='SALES' AND stale_stats='YES';

Oracle tracks DML against each table and marks statistics stale once roughly 10% of rows have changed. The auto job then refreshes the stale ones. The gap it cannot cover is timing: a huge nightly load finishes at 2 AM, the reporting runs at 6 AM, but the auto job runs at 10 PM - so reports run on stats that do not know last night's data exists. That is what the next section is for.

4. Gather After Big Changes - Don't Wait for the Job

After any operation that dramatically changes a table - a bulk load, a big purge, a partition exchange, a migration - gather statistics immediately as part of the job, rather than leaving the optimizer blind until the next maintenance window.

-- End of a nightly load job:
BEGIN
  -- ... load data ...
  DBMS_STATS.GATHER_TABLE_STATS('SALES','ORDERS_STG',
     estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/

This single habit prevents a large share of morning-after performance fires. A freshly loaded table with yesterday's statistics is the textbook cause of a report that suddenly full-scans a billion rows - closely related to the batch-window timing issues behind ORA-01555 and the tuning method in the performance tuning guide.

5. Histograms: Handling Skewed Data

Basic column statistics assume data is evenly spread. Real data rarely is. Consider an ORDER_STATUS column that is 99% 'CLOSED' and 1% 'OPEN'. Without a histogram, the optimizer assumes each status is equally likely and mis-estimates every query that filters on it.

A histogram tells the optimizer the real distribution - that 'OPEN' is rare and worth an index, while 'CLOSED' is common and better served by a full scan. Oracle creates them automatically (SIZE AUTO) on columns that are both skewed and used in predicates.

-- See which columns have histograms
SELECT column_name, histogram, num_distinct
FROM   dba_tab_col_statistics
WHERE  owner='SALES' AND table_name='ORDERS';

Histograms are powerful but bring one famous complication: bind variable peeking. The optimizer peeks at the first bind value, builds a plan suited to it, and reuses that plan for later executions with different values - so a query optimised for the rare 'OPEN' can run with a bad plan for the common 'CLOSED', or vice versa. Adaptive Cursor Sharing mitigates this in modern versions, but when you see one query with wildly different runtimes for different inputs, skew plus a histogram plus binds is the usual suspect.

6. When Fresh Stats Make Things Worse: Stability Tools

Occasionally the opposite problem strikes: statistics were fine, a regather changed them slightly, and a critical plan flipped to something worse. For tables where you have carefully tuned behaviour - or that are so volatile that 'accurate' stats are meaningless - you can take control.

6.1 Locking statistics

-- Freeze stats on a volatile staging/queue table
EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES','ORDER_QUEUE');
-- (the auto job then skips it; unlock with UNLOCK_TABLE_STATS)

Locking is right for tiny volatile tables (a queue that swings between 0 and 100,000 rows all day) where you set representative stats once and keep them, and for tables where you have deliberately set stats to shape plans.

6.2 Pending statistics: test before you commit

-- Gather into a 'pending' area WITHOUT affecting live plans
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES','ORDERS','PUBLISH','FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES','ORDERS');

-- Test in your session against the pending stats
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
-- ... run and check the plans ...

-- Happy? Publish them. Not happy? Delete them.
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES','ORDERS');

Pending statistics are the professional way to introduce new stats on a sensitive system: gather them, validate the plans they produce, and only then make them live - no more crossing your fingers after a production regather.

7. Common Statistics Mistakes

• Turning off the auto job because 'it caused a plan change once', then never gathering manually - so every table slowly drifts to stale, wrong stats. Fix the one regression with a baseline or locked stats; keep the job.
• Using ANALYZE in old scripts. Replace with DBMS_STATS everywhere.
• Hardcoding a tiny sample (estimate_percent=>1) copied from a 2005 blog - AUTO_SAMPLE_SIZE is faster and more accurate now.
• No gather after bulk loads - the single biggest cause of morning-after regressions.
• Deleting stats to 'reset' - a table with no stats forces dynamic sampling or wild guesses; gather fresh stats instead of deleting.

8. A Real Case: The Report That Broke Every Monday

A distribution client had a sales report that ran in seconds most days but crawled for 40 minutes every Monday. The pattern was the clue: a large weekend data load finished Sunday night, the automatic stats job had last run Saturday, and Monday's report therefore optimised against statistics that believed the newest, largest partition was nearly empty - so it chose a nested-loop plan meant for a few rows and executed it against millions.

The fix was one line in the load job: gather statistics on the affected partition immediately after the weekend load completed, using AUTO_SAMPLE_SIZE. The Monday slowdown vanished. We also locked stats on a small, wildly volatile lookup table that had been causing occasional plan flips, and set it representative values once. Two targeted changes, zero parameter guesswork - because the diagnosis started from the statistics, which is where query mysteries almost always end.

Frequently Asked Questions

What are optimizer statistics in Oracle?

They are stored counts and distributions about your data - table row and block counts, column distinct values, high/low values, index selectivity, and histograms - that the cost-based optimizer uses to estimate row counts and choose an execution plan. The optimizer never reads your actual data when choosing a plan; it relies entirely on these statistics.

Should I use ANALYZE or DBMS_STATS?

Always DBMS_STATS. The old ANALYZE command exists only for backward compatibility and produces statistics the modern optimizer does not fully use, and it lacks features like AUTO_SAMPLE_SIZE, histograms control, locking, and pending statistics. Replace ANALYZE with DBMS_STATS everywhere.

Why did my query get slow after gathering statistics?

New statistics can change the optimizer's estimates and flip a plan to a worse one - especially on skewed data with histograms and bind variables. Use pending statistics to test new stats before publishing them, lock statistics on carefully tuned tables, and consider SQL Plan Baselines to pin known-good plans.

What are histograms and when do I need them?

Histograms describe skewed column distributions - for example a status column that is 99% one value. They let the optimizer treat rare values (worth an index) differently from common ones (better full-scanned). Oracle creates them automatically with METHOD_OPT SIZE AUTO on columns that are both skewed and used in predicates.

When should I gather statistics manually instead of relying on the auto job?

Immediately after any operation that changes a table dramatically - bulk loads, large purges, partition exchanges, migrations. The automatic job runs in the maintenance window and marks stats stale at ~10% change, so it cannot cover the gap between a nightly load and morning reporting. Gathering as part of the load job prevents most morning-after regressions.