Enabling Automatic Statistics Gathering
Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
In situations when you want to disable automatic statistics gathering, the most direct approach is to disable the
GATHER_STATS_JOB as follows:
BEGIN DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); END; /
Well thats for 10g. But as of 11g below applies :
DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name IN VARCHAR2, operation IN VARCHAR2, window_name IN VARCHAR2);
Oracle 11g includes three automated database maintenance tasks:
- Automatic Optimizer Statistics Collection – Gathers stale or missing statistics for all schema objects (more info). The task name is ‘auto optimizer stats collection’.
- Automatic Segment Advisor – Identifies segments that could be reorganized to save space (more info). The task name is ‘auto space advisor’.
- Automatic SQL Tuning Advisor – Identifies and attempts to tune high load SQL (more info). The task name is ‘sql tuning advisor’.