10 Facts about Automatic Statistics Gathering

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




  • 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