Automatic Optimizer Statistics Collection (Oracle Database 10g to 11g Change)

posted Sep 24, 2010, 9:19 AM by Sachchida Ojha

In Oracle Database 11g Automatic optimizer statistics collection runs as part of the automated maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined maintenance windows. The AutoTask statistics collection replaces  the Oracle Database 10g GATHER_STATS_JOB. Automatic optimizer statistics collection is enabled by default. If for some reason automatic optimizer statistics collection needs to be disabled, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package.

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
 

To re-enable automatic optimizer statistics collection, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

You can query the dba_autotask_client and dba_autotask_job_history to find out the details of Automatic Optimizer Statistics Collection.

select client_name,status,TOTAL_CPU_LAST_7_DAYS
from dba_autotask_client where
client_name like 'auto optimizer stats collection';

select CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,JOB_STATUS
FROM dba_autotask_job_history where
client_name like 'auto optimizer stats collection';

Comments