Automated Maintenance Tasks in Oracle 11g

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.

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:

Oracle PL/SQL

1 2 3 4 5 6 7

SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  ENABLED   auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED

These tasks are enabled by default.

All automated maintenance tasks are scheduled to run during the Oracle Scheduler maintenance window. The Oracle Scheduler creates a job when the window (period) starts. After the automated task job completed, the Oracle Scheduler automatically drops that job.

You can view all the predefined maintenance windows by querying the DBA_AUTOTASK_SCHEDULE view:

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10

SELECT * FROM dba_autotask_schedule;   WINDOW_NAME          START_TIME    DURATION     -------------------- ------------- ----------- MONDAY_WINDOW        08/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   MONDAY_WINDOW        15/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   MONDAY_WINDOW        22/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   ...   SUNDAY_WINDOW        21/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0   SUNDAY_WINDOW        28/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::

Oracle PL/SQL

1	EXEC dbms_auto_task_admin.disable;

If you want to enable all the automated tasks, issue the following command:

Oracle PL/SQL

1	EXEC dbms_auto_task_admin.enable;

You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

EXEC dbms_auto_task_admin.disable( 'auto optimizer stats collection', NULL, NULL );   SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  DISABLED auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED   EXEC dbms_auto_task_admin.enable( 'auto optimizer stats collection', NULL, NULL );   SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  ENABLED   auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED

You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.