10g Automatic Statistics Generation

In releases prior to Oracle10g, administrators were required to schedule DBMS_STATS jobs on a regular basis to ensure that valid statistics were available to the optimizer. In releases prior to Oracle9i, it was necessary to "guess" how much of the data changed to determine if statistics collection was necessary.

In Oracle9i, the GATHER AUTO option of the DBMS_STATS procedure could be used to help determine if statistics generation was required. If more than 10% of the rows changed in the table since the last analyze was performed, the DBMS_STATS procedure (with the GATHER_AUTO option activated) analyzed the table.

By default, Oracle10G automates these tasks by evaluating the statistics for all of the tables in the database and running analyze when required. Oracle10g's default maintenance window is nightly from 10 PM to 6 AM and all day on weekends. During these time periods, statistics are automatically collected using the GATHER_STATS_JOB procedure. The maintenance window time-periods can be adjusted to tailor them to each individual application's business processing requirements.

In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.

To ensure that 10G is automatically gathering statistics for data objects, you'll need to verify that:

- The statistics job is running by executing the following SQL statement:

SELECT * FROM DBA_SCHEDULER_JOBS

WHERE JOB_NAME = 'GATHER_STATS_JOB';

- The modification monitoring feature that identifies stale statistics is enabled.

This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL (default) or ALL.

Source: DBAzine.com