How often should statistics be gathered?
In an ideal world, the Query Optimizer would have a real-time, 100% accurate picture of every row in your database. In the real world, gathering statistics is an expensive process that consumes CPU, I/O, and memory.
So, how do you find the "Goldilocks zone"—not too often, not too rarely?
For 90% of tables, the answer is: Let the Automatic Maintenance Job do its thing.
Oracle has a built-in automated task (the auto_stats_job) that runs during predefined maintenance windows (typically overnight). It is surprisingly smart:
It monitors DML activity.
It only gathers statistics on tables where more than 10% of the data has changed (the "staleness" threshold).
Verdict: If your data changes gradually throughout the day, the nightly job is usually sufficient.
There are three specific scenarios where waiting for the nightly job will lead to a performance disaster.
If you truncate a table and load 50 million rows at 10:00 AM, you cannot wait until midnight for the auto-job. Until then, the Optimizer thinks the table has 0 rows and will choose a high-speed "Nested Loop" plan that will crawl on 50 million rows.
Strategy: Gather statistics immediately after any massive INSERT, UPDATE, or LOAD operation.
Some tables are "empty-full-empty" tables (like a processing queue or a Global Temporary Table).
The Problem: If stats are gathered when the table is empty, the plan is ruined for when the table is full.
Strategy: Gather stats when the table is at its "Average" or "High-Water Mark" size, and then lock the statistics so the nightly job doesn't overwrite them with "0 rows" data.
If you use range partitioning by date, the nightly job might gather stats for today, but it won't have stats for tomorrow. When tomorrow's data starts flowing in, the Optimizer sees "Out of Range" values.
Strategy: Use Set Table Preferences to enable "Incremental Statistics" on partitioned tables. This allows Oracle to update the global stats by only scanning the new partition.
You don't have to guess if it's time to gather stats. Oracle tracks this for you in the DBA_TAB_STATISTICS view.
SELECT table_name, last_analyzed, stale_stats
FROM user_tab_statistics
WHERE stale_stats = 'YES';
If stale_stats is YES, it means the data has changed by at least 10% since the last gather, and the Optimizer's "map" is officially out of date.
| Scenario | Frequency | Recommended Action |
| Standard OLTP Tables | Daily (Auto) | Let the Nightly Maintenance Job handle it. |
| Bulk Data Loads | Per Load | EXEC DBMS_STATS.GATHER_TABLE_STATS(...) post-load. |
| Very Small Tables | Rarely | The cost of a bad plan is low; nightly is fine. |
| Temporary/Queue Tables | Once | Gather at peak size and Lock the stats. |
| Partitioned Tables | Per Partition | Use Incremental Stats to avoid full table scans. |
Some DBAs schedule a job to gather statistics on the entire database every hour "just to be safe." Don't do this.
It wastes massive amounts of system resources.
It invalidates the Library Cache. Every time you gather stats, all currently cached SQL plans for that table are thrown out, forcing the database to perform expensive "Hard Parses" for every query.
If you are worried that new statistics might break a query, you can gather them as PENDING.
EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'PUBLISH', 'FALSE');
This allows you to test the new stats in your own session to see if the plans improve before you publish them to the rest of the company.