Statistics Job

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 DBA_SCHEDULER_JOBS view:

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’.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s