Automatic Statistic Gathering

From Oracle 10g, any DML operations performed on any objects are monitored by oracle. The information is stored in the view SYS.DBA_TAB_MODIFICATIONS. Based on the amount of changes, oracle decides whether to gather new statistics for an object.

When you create the database using DBCA, oracle automatically creates a Job, GATHER_STATS_JOB, automatically and is scheduled to run during the maintenance window.

The following query is used to find whether the job is running,

SQL> Select Job_name from dba_scheduler_jobs where job_name like 'GATHER_STAT%';

Maintenance Windows: Oracle Scheduler have two Maintenance windows,

Weeknight window : Between 10 pm and 6 am (monday - friday)
Weekend window: Between 12 am saturday and 12 am sunday

The job is closed when the maintenance window closes even if the job has not finished.

To enable statistics collection to continue even after the maintenance window closes, set the stop_on_window_close attribute to false.

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE (‘GATHER_STATS_JOB’, ‘STOP_ON_WINDOW_CLOSE’, ‘FALSE’);

Candidates for Statistics Gathering: The job gathers statistics for objects with

Missing Statistics : Objects without statistics
Stale Statistics : If more than 10% of the rows in an object is modified, its statistics are considered stale.

Note: System Statistics and the Fixed table (X$ tables) statistics are not gathered automatically.

To Disable Automatic Statistic Gathering

SQL> Exec DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);

To get the details about the maintenance window

SQL> Select window_name, repeat_interval, duration, next_start_date
From dba_scheduler_windows;

To change the period of Maintenance Windows

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE(‘WEEKNIGHT_WINDOW’, ‘repeat_interval’, 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0; byminute=0; bysecond=0');

Lock Statistics: If you dont want to gather statistics for certain object or schema then you can lock it as follows,

SQL> Exec DBMS_STATS.LOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.LOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

To unlock

SQL> Exec DBMS_STATS.UNLOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.UNLOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

Note: When you perform bulk loads on tables gather statistics on those tables immediately.

Thanks