Automatic Statistics Gathering

•Before 10g you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. It is good to remember that starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

•The job GATHER_STATS_JOB automatically gather optimizer statistics.

•This job gathers statistics on all objects in the database which have either
-Missing statistics or
-Stale statistics

•This job is created automatically at database creation time and is managed by the Scheduler. By default GATHER_STATS_JOB runs every night from 10 P.M. to 6 A.M. and all day on weekends if missing statistics or stale statistics found.

•In fact the Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.

•The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.

•Database automatically collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

•If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL then database automatically gather statistics for the objects which has stale statistics. If it is set to BASIC then then the automatic statistics gathering job is not able to detect stale statistics.

•To know about job GATHER_JOB_STATS issue the following query,
SQL>select JOB_TYPE,SCHEDULE_TYPE,START_DATE,REPEAT_INTERVAL,END_DATE,ENABLED,STATE,RUN_COUNT FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

JOB_TYPE SCHEDULE_TYP START_DATE REPEA END_DATE ENABL STATE RUN_COUNT
---------------- ------------ ---------- ----- ---------- ----- --------- ----------
WINDOW_GROUP TRUE SCHEDULED 31

To know the database creation date issue,
SQL> select created, sysdate from v$database;
CREATED SYSDATE
--------- ---------
06-MAY-08 12-JUN-08

So between May 06 and Jun 08 this job ran 31 times.

•In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. If STATISTICS_LEVEL is set to TYPICAL or ALL then monitoring is enabled. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view.

Like you can query,
select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS;

•Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

Example:
---------------
SQL> insert into test values('hi');
1 row created.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
no rows selected

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TEST 1 0 0

After analyze they will disappear,
SQL> analyze table test estimate statistics;
Table analyzed.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
no rows selected

•If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.