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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s