Oracle 11g 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.


Automated Tasks:


In oracle Database 10g, we had only the first two automatic maintenance tasks mentioned below. With Oracle 11g, we have a third task name Automatic SQL Tunning Advisor

• Automatic Optimizer Statistics Collection

Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.for more information, visit the link below
BASH-DBA: Oracle 11g : Types of Statistics Collected

• Automatic Segment Advisor

Identifies segments that have space available for reclamation, and makes recommendations on how to de-fragment those segments.

• Automatic SQL Tuning Advisor

Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements. 


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

• 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

To view the task, run the below SQL

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 derived 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;



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.


How to configure (disable/enable) the Automated Maintenance Tasks

DBMS_AUTO_TASK_ADMIN PL/SQL package can be used to do the following.


To enable the task

BEGIN

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

To enable it

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

Use the following query to view the possible values for client_name 

SQL> select client_name from dba_autotask_client;

CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor

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;

Use the following query to view the window_name possible values mentioned above 

SQL> select window_name from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME
------------------------------
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW

By default, all the maintenance tasks run in all pre-defined windows 

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     => 'TEST_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 => 'TEST_WINDOW');
END;

SQL> select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;


WINDOW_NAME                    WINDOW_NEXT_TIME
------------------------------ ---------------------------------------------------------------------------
MONDAY_WINDOW                  14-MAY-12 10.00.00.000000 PM EST5EDT
TUESDAY_WINDOW                 08-MAY-12 10.00.00.000000 PM EST5EDT
WEDNESDAY_WINDOW               09-MAY-12 10.00.00.000000 PM EST5EDT
THURSDAY_WINDOW                10-MAY-12 10.00.00.000000 PM EST5EDT
FRIDAY_WINDOW                  11-MAY-12 10.00.00.000000 PM EST5EDT
SATURDAY_WINDOW                12-MAY-12 06.00.00.000000 AM EST5EDT
SUNDAY_WINDOW                  13-MAY-12 06.00.00.000000 AM EST5EDT
TEST_WINDOW                    09-MAY-12 05.00.00.000000 AM EST5EDT

Removing the Maintenance Window:




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

Data Dictionary Views:

DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_CLIENT_HISTORY




source: Oracle Documentation