Auto-Task (Oracle Database 10g to 11g Change)

posted Sep 24, 2010, 12:02 PM by Sachchida Ojha
In Oracle Database 11g the automated maintenance tasks infrastructure known as AutoTask enables Oracle Database to automatically schedule Automatic Maintenance Tasks.  AutoTask schedules automatic maintenance tasks to run in a  set of Oracle Scheduler windows known as maintenance windows.  Maintenance windows are those windows that are members of the Oracle Scheduler  window group MAINTENANCE_WINDOW_GROUP.

The following are the tasks that AutoTask automatically schedules in these maintenance windows:

    * Optimizer statistics gathering
    * Automatic Segment Advisor
    * SQL Tuning Advisor

By default there are seven predefined maintenance windows, each one representing a day of the week.   The weekend maintenance windows, SATURDAY_WINDOW and SUNDAY_WINDOW, are longer  in duration than the weekday maintenance windows.  The window group MAINTENANCE_WINDOW_GROUP consists of these seven windows.
Window Name Description      
MONDAY_WINDOW     Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW     Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW     Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW     Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW     Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW     Starts at 6 a.m. on Saturday and is 20 hours long
SUNDAY_WINDOW     Starts at 6 a.m. on Sunday and is 20 hours long.


Auto-Task provides the necessary infrastructure to handle large numbers of jobs.  Auto-Task provides out-of-the-box management of resource distribution (CPU and I/O) among the various database maintenance tasks such as Automatic Optimizer Statistics Collection, Automatic Segment Advisor, and others.  The CPU is automatically managed. I/O is managed only if the I/O Resource Manager is enabled.

Auto-Task ensures that work during maintenance operations is not affected and that user activity gets the necessary resources to complete.

You can disable a particular automated maintenance tasks for all maintenance windows with a single operation. You do so by calling the DISABLE procedure of the DBMS_AUTO_TASK_ADMIN PL/SQL package without supplying the window_name argument.  For example, you can completely disable the Automatic SQL Tuning Advisor task as follows:

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

To enable this maintenance task again, use the ENABLE procedure, as follows:

BEGIN
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

The task names to use for the client_name argument are listed in the DBA_AUTOTASK_CLIENT database dictionary view.

To enable or disable all automated maintenance tasks for all windows, call the ENABLE or DISABLE procedure with no arguments.

EXECUTE DBMS_AUTO_TASK_ADMIN.ENABLE;
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

Automatic Maintenance Tasks Management is automatically enabled when upgrading to Oracle Database 11g. If you disable either Optimizer Statistics Gathering or Segment Advisor jobs in Oracle Database 10g, then the corresponding Automatic Maintenance Tasks Management feature is disabled after upgrading to Oracle Database 11g.
Comments