Variety of E-Tips for IT Professional

Is your database grinding to a halt? Try disabling automatic statistics gathering (Oracle 10g)

Oracle 10g introduced new functionality with a supplied package named DBMS_SCHEDULER. This allows you to create jobs and schedules that you can call from PL/SQL programs. Unfortunately, sometimes certain automatic jobs that Oracle enables during installation can conflict with other things you're trying to do. Hence, it's good to know what scheduled jobs are running and how to disable them.

For instance, by default, DBMS_SCHEDULER automatically executes a process that collects statistics with a predefined job named GATHER_STATS_JOB from 10 p.m. to 6 a.m. on weekdays. This job calls a program named GATHER_STATS_PROG, which in turn calls DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC. This procedure gathers statistics on all objects in the database with missing statistics or stale statistics.

During a recent production evening, a certain site's production system began to slow to a crawl. The reason: The automatic GATHER_STATS_JOB conflicted with the routine statistics collected by the regular overnight statistics gathering. To resolve the problem, we disabled the automatic database statistics gathering with the DISABLE procedure, like so:

begin
dbms_scheduler.disable('GATHER_STATS_JOB');
end;