How To Disable Automatic Statistics Collection in Oracle 10G

To disable the automatic statistics collection, you can execute the following procedure:

exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

To check whether the job is disabled, run the following Query:

select state from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’;

The job details can be viewed by querying the DBA_SCHEDULER_JOBS view:

select job_name, job_type, program_name, schedule_name, job_class
from dba_scheduler_jobs
where job_name = ‘GATHER_STATS_JOB’;

The output will show that the job schedules a program called ‘GATHER_STATS_PROG’ in the ‘MAINTENANCE_WINDOW_GROUP’ time schedule. The program named ‘GATHER_STATS_PROG’ starts the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC stored procedure:

select program_action
from dba_scheduler_programs
where program_name = ‘GATHER_STATS_PROG’;

The job is scheduled according to the value of the SCHEDULE_NAME field. In this example, the scheduled being used is: ‘MAINTENANCE_WINDOW_GROUP’. This schedule is defined in the DBA_SCHEDULER_WINGROUP_MEMBERS view:

select *
from dba_scheduler_wingroup_members
where window_group_name = ‘MAINTENANCE_WINDOW_GROUP’;

The meaning of these ‘WINDOWS’ can be found in ‘DBA_SCHEDULER_WINDOWS’:

select window_name, repeat_interval, duration
from dba_scheduler_windows
where window_name in (’WEEKNIGHT_WINDOW’,'WEEKEND_WINDOW’);

The meaning of these entries is as follows:

The WEEKNIGHT_WINDOW is schedule each week day at 10PM and should last a maximum of 8 hours. The WEEKEND_WINDOW is scheduled Saturday at 0AM and should last 2 days maximum. If the START_DATE and END_DATE columns (not shown) are NULL, then this job will run continuously. All these definitions can be found in the $ORACLE_HOME/rdbms/admin/catmwin.sql script.

The point of illustrating the Automatic Statistics Collection in Oracle 10g+ was simply to show how to check it the delivered stats job is enabled and if you need to disable or turn it off how to do so. In our case we disable the delivered job for our production databases because we execute our own custom scripts via crontab at least once a day and in some cases twice a day. We are running PeopleSoft Financials as well as HRMS. In regards to PeopleSoft Financials we rely on nVision for generating and delivering Financial Reports and we are using dynamic tree selectors. If we let Oracle generate the statistics for us via the delivered scheduled jobs some of our nVision Report Books which consist of up to 10 nVision Reports would never finish generating the report. At the end of our custom shell script to generate schema and system dictionary statistics for the CBO we delete the statistics on the PSTREESELECTxx tables. As a result the majority of our nVision Reports execute in less than 2 minutes and the Report Books with 10 nVision Reports execute in less than 20 minutes for all 10 reports.

Personally in order to ensure the best possible performance in Oracle 10g R2+ I believe it is best to keep the CBO statistics as current as possible.