Disable Oracle Statistics

Both Oracle 10g and 11g has predefined jobs/tasks for gathering statistics. Oracle's column histogram can cause performance problems therefore IFS recommends disabling the Oracle jobs in favor of the IFS job. Either way, you should only run one of these. Either the IFS one the Oracle one, not both.

Oracle 10g

A default scheduled job called GATHER_STATS_JOB runs the program GATHER_STATS_PROG to gather statistics. Other jobs may be scheduled using Enterprise Manager or the DBMS_SCHEDULER API.

You may view the status of scheduled statistics jobs this way:

SELECT job_name,enabled,state,last_start_date,next_run_date
FROM dba_scheduler_jobs
WHERE program_name='GATHER_STATS_PROG';

You may disable a job this way:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;

If you want to remove the job, replace DISABLE by REMOVE.

Oracle 11g

A default autotask now gathers the statistics. The task (client) is called auto optimizer stats collection.

You may view the status of autotask this way:

SELECT * FROM dba_autotask_client;

or

SELECT * FROM dba_autotask_operation;

You may disable the statistics autotask this way:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(‘auto optimizer stats collection’,null,null);
END;