Automatic Optimizer Statistics Collection

http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php#automatic_optimizer_statistics_collection

By default Oracle 10g automatically gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB.

By default this job runs within a maintenance windows between 10 P.M. to 6 A.M. week nights and all day on weekends.

The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option.

The main difference is that the internal job prioritizes the work such that tables most urgently requiring statistics updates are processed first.

In some cases automatically gathering statistics can cause problems.

Highly volatile tables and load tables may have their statistics gathered when there is an unrepresentative number of rows present.

 These situations can be avoided by using one of two methods.

• The current statistics can be deleted and locked to prevent DBMS_STATS from gathering new statistics. If the OPTIMIZER_DYNAMIC_SAMPLING parameter is set to 2 (the default) or higher the necessary statistics will be gathered as part of the query optimization stage

·                BEGIN

·                  DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE');

·                  DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');

·                END;

/

• The statistics can be gathered then locked at a time when the table contains the appropriate data.

·                BEGIN

·                  DBMS_STATS.gather_table_stats('MY_SCHEMA','LOAD_TABLE');

·                  DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');

·                END;

/