Disable Automatic Statistics Collection in 10G

Disable Automatic Statistics Collection in 10G.

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