I regularly receive inquiries about automatic statistics gathering which is controlled by a default job (gather_stats_job) installed with any new 10G/11G database.
Here are some facts I’ve compiled so far:
- Job runs daily in 10g, weekly in 11g
- Job runs in the default maintenance window (opened from 10pm to 6am)
- Gather statistics on objects with missing or stale statistics
- Can be resource intensive depending on daily activity
- Objects that are in most need are processed first
- The default gathering parameters can be modified using DBMS_STATS.SET_PARAM
- Run DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’) to disable the job
- For highly volatile tables (truncated once or many times during the day), it’s preferrable to set the statistics to NULL and let the dynamic sampling kick in, use DBMS_STATS.DELETE_STATS and LOCK_TABLE_STATS
- Statistics are not gathered on external tables, they must be gathered individually using DBMS_STATS.GATHER_TABLE_STATS
- Statistics are not gathered on fixed objects , use GATHER_FIXED_OBJECTS_STATS during a representative workload
