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
| Level | Examples |
| Table | Number of rows, blocks, average row length |
| Column | Distinct values, min, max, nulls, histogram |
| Index | Levels, leaf blocks, clustering factor |
| System | CPU 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.