disable statistics with gather_stats_job

Oracle Tips by Burleson Consulting

First, learn about gather_stats_job here:

Using gather_stats_job

Oracle Metalink notes that there are some DBA activities that require disabling the automatic statistics collection and provides this disable script:

As the SYS user, use the following query to check for this job:

SQL> SELECT STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = ’GATHER_STATS_JOB’;

If not disabled, use the following command to disable the job:

SQL> exec dbms_scheduler.disable(’SYS.GATHER_STATS_JOB’);

Robert Freeman has more on the gather_stats_job procedure.

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when you create a new Oracle database under Oracle Database 10g. To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:

SELECT
   a.job_name,
   a.enabled,
   c.window_name,
   c.schedule_name,
   c.start_date,
   c.repeat_interval
FROM
   dba_scheduler_jobs             a,
   dba_scheduler_wingroup_members b,
   dba_scheduler_windows          c
WHERE
   job_name=’GATHER_STATS_JOB’
And
   a.schedule_name=b.window_group_name
And
   b.window_name=c.window_name;