All you need to do to make sure the automatic statistics collection process works is to ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

Oracle will use the DBMS_STATS package to collect optimizer statistics on an automatic basis.

Oracle Database 10g introduces new values for the GRANULARITY and DEGREE arguments of the GATHER_*_STATS procedures to simplify the determination of the calculated statistics. Unless you are an experienced user, you should use the new default values:

• GRANULARITY

o AUTO (default): The procedure determines the granularity based on the    partitioning type. It collects the global-, partition-, and sub-partition level    statistics if the subpartitioning method is LIST.

   Otherwise, it collects only the global- and partition-level statistics.

o GLOBAL AND PARTITION: Gathers the global- and partition-level    statistics. No subpartition-level statistics are gathered even if it is a    composite partitioned object.

• DEGREE

o AUTO_DEGREE: This value enables the Oracle server to decide the    degree of parallelism automatically. It is either 1 (serial execution) or    DEFAULT_DEGREE (the system default value based on the number of    CPUs and initialization parameters) according to the size of the object.

Oracle automatically creates a database job called GATHER_STATS_JOB at database creation time.

Oracle automatically schedules the GATHER_STATS_JOB job to run when the maintenance window opens.

The GATHER_STATS_JOB job calls the procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to gather the optimizer statistics.

The job collects statistics only for objects with missing statistics and objects with stale statistics.

If you want to stop the automatic gathering of statistics:

1. click the Administration tab.

2. Scheduler Group -> Windows Link

3. Click the Edit button. You’ll then be able to edit the weeknight or the weekend window     timings.

You cannot use the ALTER_DATABASE_TAB_MONITORING and ALTER_SCHEMA_TAB_MONITORING procedures of the DBMS_STATS package to turn table monitoring on and off at the database and schema level, respectively, because these subprograms are deprecated in Oracle Database 10g. Oracle 10g automatically performs these functions, if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

Oracle 10g allows you to gather Optimizer statistics manually using the DBMS_STATS.

You can lock statistics of specific objects so that current object statistics will be used by the optimizer regardless of data changes on the locked objects.

Use the following procedures in DBMS_STATS

o LOCK_TABLE_STATISTICS
o UNLOCK_TABLE_STATISTICS
o LOCK_SCHEMA_STATISTICS
o UNLOCK_SCHEMA_STATISTICS

Example:

You may want Oracle to override any existing statistics locks. You can do so by setting the FORCE argument with several procedures to TRUE in the DBMS_STATS package.
The default is FALSE.

Fortunately, Oracle lets you automatically save all old statistics whenever your refresh the statistics.

You can restore statistics by using the appropriate RESTORE_*_STATS procedures.

The view DBA_OPTSTAT_OPERATIONS contains a history of all optimizer statistics collections.

DBA_TAB_STATS_HISTORY - This view contains a record of all changes made to table statistics. By default, the DBA_TAB_STATS_HISTORY view saves the statistics history for 31 days. However, by using the ALTER_STATS_HISTORY_RETENTION procedure of the DBMS_STATS package, you can change the default value of the statistics history retention interval.

RBO still exists in Oracle Database 10g but is an unsupported feature. No code changes have been made to RBO, and no bug fixes are provided.