Managing Automated Maintenance Tasks

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. These tasks run automatically by the database and are executed during the "maintenance window" which is a contiguous time interval during which automated maintenance tasks are run under.
Oracle Database 11g comes with three predefined automatic maintenance tasks:

• The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
• The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
• Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.

The Automated Maintenance Tasks are managed by the AutoTask Background Process (ABP). Following are the process functionalties:

•  It converts automatic tasks into Scheduler jobs. It does not execute the maintenance tasks.
•  It determines the jobs that need to be created for each maintenance task window.
•  It stores task execution history in the SYSAUX tablespace

Task Information
 To view the task, run the below SQL
select client_name from DBA_AUTOTASK_CLIENT;

CLIENT_NAME
auto optimizer stats collection
auto space advisor
sql tuning advisor

In Oracle database 11g, there are seven predefined maintenance windows. Their names are from MONDAY_WINDOW to SUNDAY_WINDOW.

To display list schedule of the predefined maintenance windows in the next 32 days, issue the following query:

SELECT WINDOW_NAME, to_char(START_TIME,'DD-Mon-RR hh24:mi') START_TIME, DURATION
FROM DBA_AUTOTASK_SCHEDULE
ORDER BY WINDOW_NAME, START_TIME DESC;

WINDOW_NAME	START_TIME	DURATION
FRIDAY_WINDOW	26-Apr-13 22:00	+00 04:00:00.000000
FRIDAY_WINDOW	19-Apr-13 22:00	+00 04:00:00.000000
FRIDAY_WINDOW	12-Apr-13 22:00	+00 04:00:00.000000
FRIDAY_WINDOW	03-May-13 22:00	+00 04:00:00.000000
MONDAY_WINDOW	29-Apr-13 22:00	+00 04:00:00.000000
MONDAY_WINDOW	22-Apr-13 22:00	+00 04:00:00.000000
MONDAY_WINDOW	15-Apr-13 22:00	+00 04:00:00.000000
MONDAY_WINDOW	08-Apr-13 22:00	+00 04:00:00.000000
MONDAY_WINDOW	06-May-13 22:00	+00 04:00:00.000000
SATURDAY_WINDOW	27-Apr-13 06:00	+00 20:00:00.000000
SATURDAY_WINDOW	20-Apr-13 06:00	+00 20:00:00.000000
SATURDAY_WINDOW	13-Apr-13 06:00	+00 20:00:00.000000
SATURDAY_WINDOW	04-May-13 06:00	+00 20:00:00.000000
SUNDAY_WINDOW	28-Apr-13 06:00	+00 20:00:00.000000
SUNDAY_WINDOW	21-Apr-13 06:00	+00 20:00:00.000000
SUNDAY_WINDOW	14-Apr-13 06:00	+00 20:00:00.000000
SUNDAY_WINDOW	05-May-13 06:00	+00 20:00:00.000000
THURSDAY_WINDOW	25-Apr-13 22:00	+00 04:00:00.000000
THURSDAY_WINDOW	18-Apr-13 22:00	+00 04:00:00.000000
THURSDAY_WINDOW	11-Apr-13 22:00	+00 04:00:00.000000
THURSDAY_WINDOW	02-May-13 22:00	+00 04:00:00.000000
TUESDAY_WINDOW	30-Apr-13 22:00	+00 04:00:00.000000
TUESDAY_WINDOW	23-Apr-13 22:00	+00 04:00:00.000000
TUESDAY_WINDOW	16-Apr-13 22:00	+00 04:00:00.000000
TUESDAY_WINDOW	09-Apr-13 22:00	+00 04:00:00.000000
TUESDAY_WINDOW	07-May-13 22:00	+00 04:00:00.000000
WEDNESDAY_WINDOW	24-Apr-13 22:00	+00 04:00:00.000000
WEDNESDAY_WINDOW	17-Apr-13 22:00	+00 04:00:00.000000
WEDNESDAY_WINDOW	10-Apr-13 22:00	+00 04:00:00.000000
WEDNESDAY_WINDOW	08-May-13 22:00	+00 04:00:00.000000
WEDNESDAY_WINDOW	01-May-13 22:00	+00 04:00:00.000000

Weekdays windows open at 10:00 p.m. and close at 2:00 a.m (4 hours). The weekend windows (Saturday and Sunday) open at 6:00 a.m. and close in 20 hours.

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

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


Check the status

 select * from DBA_AUTOTASK_OPERATION;

CLIENT_NAME	OPERATION_NAME	STATUS
auto optimizer stats collection	auto optimizer stats job	ENABLED
auto space advisor	auto space advisor job	ENABLED
sql tuning advisor	automatic sql tuning task	ENABLED

By default, all the maintenance tasks run in all pre-defined windows. However, if you want to enable or disable the task for a "specific window", use the below script


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

 
Creating the new Maintenance Window

To create a maintenance window, you must create a Scheduler window and then add it to the window group MAINTENANCE_WINDOW_GROUP

The DBMS_SCHEDULER PL/SQL package provides the ADD_WINDOW_GROUP_MEMBER subprogram, which adds a window to a window group

BEGIN
dbms_scheduler.create_window(
    window_name     => 'HOME_WINDOW',
    duration        =>  numtodsinterval(1, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
    dbms_scheduler.add_window_group_member(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'HOME_WINDOW');
END;


select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME	WINDOW_NEXT_TIME
MONDAY_WINDOW	4/8/2013 10:00:00.000000 PM +03:00
TUESDAY_WINDOW	4/9/2013 10:00:00.000000 PM +03:00
WEDNESDAY_WINDOW	12/28/2011 10:00:00.000000 PM +03:00
THURSDAY_WINDOW	4/11/2013 10:00:00.000000 PM +03:00
FRIDAY_WINDOW	4/12/2013 10:00:00.000000 PM +03:00
SATURDAY_WINDOW	4/13/2013 06:00:00.000000 AM +03:00
SUNDAY_WINDOW	4/7/2013 06:00:00.000000 AM +03:00
HOME_WINDOW	4/8/2013 05:00:00.000000 AM +03:00

Removing the Maintenance Window:

BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'HOME_WINDOW');
END;

Relevant Views

The following views display information related to the automated database maintenance tasks:

• DBA_AUTOTASK_CLIENT
• DBA_AUTOTASK_CLIENT_HISTORY
• DBA_AUTOTASK_CLIENT_JOB
• DBA_AUTOTASK_JOB_HISTORY
• DBA_AUTOTASK_OPERATION
• DBA_AUTOTASK_SCHEDULE
• DBA_AUTOTASK_TASK
• DBA_AUTOTASK_WINDOW_CLIENTS
• DBA_AUTOTASK_WINDOW_HISTORY