Oracle 11g Automated Maintenance Tasks

Three tasks are executed as part of automated maintenance task. They are

Optimizer Statistics Gathering
Segment Advisor
Automatic SQL Tuning

By default the above tasks are enabled on the database. If required these tasks can be disabled for each week or for a particular day.

To disable the optimizer statistics collection, use the DBMS_AUTO_TASK_ADMIN package as shown below

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

PL/SQL procedure successfully completed.

SQL>


To disable for a particular day for example Thursday

SQL> BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;


PL/SQL procedure successfully completed.

SQL>

To disable the segment advisor,

BEGIN
dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
END;

PL/SQL procedure successfully completed.

SQL>


Similarly to enable them use dbms_auto_task_admin.enable procedure.


By default the automated maintenance tasks starts at 10pm every day. Sometimes there might be some batch jobs running on the server so we may need to change the time for the maintenance task. To change the timing, The task needs to be disabled first.

This could be achieved by using DBMS_SCHEDULER package. For example if we want to start the maintenance task at 11pm on Thursdays then

SQL> BEGIN
2 DBMS_SCHEDULER.DISABLE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 force=>TRUE);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL>


SQL> BEGIN
2 DBMS_SCHEDULER.SET_ATTRIBUTE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 attribute=>'REPEAT_INTERVAL',
5 value=>'FREQ=WEEKLY;BYDAY=THU;BYHOUR=23;BYMINUTE=0;BYSECOND=0');
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

SQL> BEGIN
2 DBMS_SCHEDULER.ENABLE(
3 name=>'"SYS"."THURSDAY_WINDOW"',
4 force=>TRUE);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL>