Automatic Statistics Collection in 10G

How to Disable Automatic Statistics Collection in 10G ? [ID 311836.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Information in this document applies to any platform.

Goal

How to Disable Automatic Statistics Collection in 10G ?
Note
The Automatic Statistics collection feature is enabled by default in 10G.
You can verify this by checking the following :
SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
---------------
SCHEDULED

Solution

1)To Disable the automatic statistics collection in 10G , you can execute the following procedure as sysdba:
EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
2) To check whether the job is disabled, run the following QUERY:
SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
---------------
DISABLED