Authomatic statistics gathering

Oracle 10g has a feature where it gathers optimizer statistics automatically at night. This is done through a scheduled job called GATHER_STATS_JOB. If you want to disable this, you have to issue :

EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’);

To enable it again :

EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’);

The following query shows when this job was run :

SELECT * FROM dba_scheduler_job_run_details WHERE job_name = ’GATHER_STATS_JOB’;

There are other tables, all starting with dba_scheduler, that show the schedules and so on. One of the more interesting ones is dba_scheduler_jobs.