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

 

  • 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
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s