Oracle Automatic Maintenance Tasks

Introduction

The Oracle automatic maintenance tasks were introduced in version 11g in order to automate tasks that Oracle DBAs used to do manually in the past (i.e. in earlier versions such as 9i, 10g).
This includes the following predefined automated maintenance tasks:

• Automatic Optimizer Statistics Collection - Collecting optimizer statistics for objects with stale or missing statistics
• Automatics Segment Advisor - Collecting storage-related information for segments in order to provide Segment Recommendations on those segments
• Automatic SQL Tuning Advisor - Collecting performance-related information for SQL Statements in order to provide SQL Tuning recommendations

In Oracle 12c the following maintenance task has been added:

• SQL Plan Management (SPM) Evolve Advisor - Collecting information about SQL execution plans in order to automatically evolve execution plans as part of the SQL Plan Management feature

Demonstration

You can query DBA_AUTOTASK_OERATION in order to see all the automatic maintenance tasks along with their status (whether it's enabled or disabled):

You may ask yourself when these jobs are actually running?
The answer is during the Maintenance Windows 

You can check the maintenance windows that are defined for each automatic task using DBA_AUTOTASK_WINDOW_CLIENTS

Following is an example:

The above are 7 predefined maintenance windows that are enabled by default when you install Oracle. If you'd like to see more information for each window you can query DBA_SCHEDULER_WINDOWS and it will display the exact time and duration for each window as well as the associated resource plan. You can modify the maintanence window settings using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package. Read more here.

Please note that during the time window Oracle will create a scheduler job for the automatic task and once the job is completed Oracle will drop it, so don't be surprised that you can find the jobs in DBA_SCHEDULER_JOBS. You can find historical automated tasks information using DBA_AUTOTASK_JOB_HISTORY below is an example:

You can manually disable (or enable) all the maintenance tasks using the DBMS_AUTO_TASK_ADMIN package, for example:
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

You can also disable or enable a particular maintenance task. Following is an example of how to disable the sql tuning advisor maintenance task
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

If you want to disable to enable a particular maintenance task only for a particular maintenance window, you can use the "window_name" parameter. Following is an example of how to disable the sql tuning advisor maintenance task only for the SATURDAY_WINDOW:
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => 'SATURDAY_WINDOW');
END;
/

Summary

The goal of this post was to shed light on the automatic maintenance tasks feature which is very useful but you need to aware of its behavior. In this post, I've covered the following items:

• Pre-defined automatic maintenance tasks in 11g & 12c
• Maintanence windows and how to determine their start time and duration
• Viewing historical automated tasks information (time, status, etc.)
• Disabling/enabling automated tasks