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