Optimizer Statistics with DBMS_STATS

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Intermediate
Optimizer Statistics with DBMS_STATS
Intermediate Topic 1 of 7

Why Statistics Matter

When you run a query, the cost-based optimizer chooses a plan. To choose well it needs facts about your data: how many rows, how many distinct values, how skewed. These facts are the optimizer statistics.

Simple example: A taxi driver chooses the route using traffic information. With old traffic information he picks a road that is now jammed. Old statistics make the optimizer pick bad plans in the same way.

What is Collected

LevelExamples
TableNumber of rows, blocks, average row length
ColumnDistinct values, min, max, nulls, histogram
IndexLevels, leaf blocks, clustering factor
SystemCPU speed and I/O speed

Check Existing Statistics

sql SELECT table_name, num_rows, blocks, last_analyzed, stale_stats FROM dba_tab_statistics WHERE owner = 'SHOP';

Gather Statistics

sql BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SHOP', tabname => 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); -- also the table's indexes END; / EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SHOP'); EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Use the defaults: AUTO_SAMPLE_SIZE and SIZE AUTO are the recommended settings. Do not use a small fixed sample percentage unless you have a reason.

Automatic Statistics Job

Oracle already gathers statistics for you every night in the maintenance window, for objects with stale or missing statistics.

sql SELECT client_name, status FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';
When to gather manually: After a big data load, after truncating and refilling a table, or when a new index is created on a very large table. Do it right after the load, before users run reports.

Stale Statistics

sql EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT table_name, stale_stats, last_analyzed FROM dba_tab_statistics WHERE owner = 'SHOP' AND stale_stats = 'YES';

Statistics become stale when more than 10% of the rows have changed.

Histograms

A histogram tells the optimizer that data is skewed. For example, 95% of orders may have status SHIPPED. With a histogram, Oracle uses an index for the rare value CANCELLED and a full scan for SHIPPED.

sql SELECT column_name, num_distinct, histogram FROM dba_tab_col_statistics WHERE owner = 'SHOP' AND table_name = 'ORDERS';

Protect Good Statistics

sql -- stop automatic changes to a table EXEC DBMS_STATS.LOCK_TABLE_STATS('SHOP','ORDERS'); EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SHOP','ORDERS'); -- go back to how statistics were yesterday BEGIN DBMS_STATS.RESTORE_TABLE_STATS('SHOP','ORDERS', SYSTIMESTAMP - INTERVAL '1' DAY); END; /
Safety net: Oracle keeps old statistics for 31 days by default. If a new gather makes a query slow, restore the previous statistics first and then investigate.

Conclusion

Good statistics lead to good plans. Keep the automatic job on, gather manually after big loads and use restore if a gather causes a regression.

Quick Summary: DBMS_STATS gathers, stale_stats shows what is old, lock and restore protect you.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators