Automated Database Maintenance Tasks

Oracle Database has automated several common maintenance tasks typically performed by database administrators.
These automated maintenance tasks are performed when the system load is expected to be light
Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database.

Oracle Database has these predefined automated maintenance tasks:

  1=>  Automatic Optimizer Statistics Collection
         Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics.
  2=>  Optimizer Statistics Advisor
         Analyzes how statistics are being gathered and suggests changes that can be made to fine tune statistics collection.
  3=>  Automatic Segment Advisor
         Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments.
  4=>  Automatic SQL Tuning Advisor
         Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements.
  5=> SQL Plan Management (SPM) Evolve Advisor
        Evolves plans that have recently been added to the SQL plan baseline.

Details of Auto-Maintenance Task

1=>  Automatic Optimizer Statistics Collection

Oracle Database can gather optimizer statistics automatically.
The automated maintenance tasks infrastructure (known as AutoTask) schedules tasks to run automatically in Oracle Scheduler windows known as maintenance windows.

By default, one window is scheduled for each day of the week. Automatic optimizer statistics collection runs as part of AutoTask.
By default, the collection runs in all predefined maintenance windows.
To collect the optimizer statistics, the database calls an internal procedure that operates similarly to the GATHER_DATABASE_STATS procedure with the GATHER AUTO option.
Automatic statistics collection honors all preferences set in the database.

The principal difference between manual and automatic collection is that the latter prioritizes database objects that need statistics.
Before the maintenance window closes, automatic collection assesses all objects and prioritizes objects that have no statistics or very old statistics.

Note:

When gathering statistics manually, you can reproduce the object prioritization of automatic collection by using the DBMS_AUTO_TASK_IMMEDIATE package.
This package runs the same statistics gathering job that is executed during the automatic nightly statistics gathering job.

Configuring Automatic Optimizer Statistics Collection from the Command Line

To configure Auto Optimizer Stats Collections we can use below options.

Run the ENABLE or DISABLE procedure in the DBMS_AUTO_TASK_ADMIN PL/SQL package.

Set the STATISTICS_LEVEL initialization level to BASIC to disable collection of all advisories and statistics, including Automatic SQL Tuning Advisor.

To enable the automated task, execute the following PL/SQL block:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL
);
END;
/

To disable the automated task, execute the following PL/SQL block:

    BEGIN
      DBMS_AUTO_TASK_ADMIN.DISABLE (  
        client_name  => 'auto optimizer stats collection'
    ,   operation    => NULL
    ,   window_name  => NULL
    );
    END;
    /

Query the data dictionary to confirm the change.

For example, query DBA_AUTOTASK_CLIENT as follows:

COL CLIENT_NAME FORMAT a31

SQL> SELECT CLIENT_NAME,STATUS,ATTRIBUTES,WINDOW_GROUP FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME					   STATUS		ATTRIBUTES						     WINDOW_GROUP
-------------------------------------------------- -------------------- -------------------------------------------------------
sql tuning advisor				   ENABLED		ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL	     ORA$AT_WGRP_SQ
auto optimizer stats collection 		   ENABLED		ON BY DEFAULT, VOLATILE, SAFE TO KILL			     ORA$AT_WGRP_OS
auto space advisor				   ENABLED		ON BY DEFAULT, VOLATILE, SAFE TO KILL			     ORA$AT_WGRP_SA

To change the window attributes for automatic statistics collection:    Change the attributes of the maintenance window as needed.

For example, to change the Monday maintenance window so that it starts at 5 a.m., execute the following PL/SQL program:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    'MONDAY_WINDOW'
,   'repeat_interval'
,   'freq=daily;byday=MON;byhour=05;byminute=0;bysecond=0'
);
END;
/

 2=>  Optimizer Statistics Advisor

Optimizer Statistics Advisor analyzes how optimizer statistics are gathered, and then makes recommendations.

Optimizer Statistics Advisor is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks.

The advisor task runs automatically in the maintenance window, but you can also run it on demand.
You can then view the advisor report. If the advisor makes recommendations, then in some cases you can run system-generated scripts to implement them.

Optimizer Statistics Advisor supports both an automated and manual mode.

    Automated

   The predefined task AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window once per day.
  The task runs as part of the automatic optimizer statistics collection client.
  The automated task generates findings and recommendations, but does not implement actions automatically.

   As for any other task, you can configure the automated task, and generate reports. If the report recommends actions, 
   then you can implement the actions manually.

If AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window, then your workflow begins by querying the report.
In the manual workflow, you must use PL/SQL to create and execute the tasks.

 Generating a Report for an Optimizer Statistics Advisor Task

The DBMS_STATS.REPORT_ADVISOR_TASK function generates a report for an Optimizer Statistics Advisor task.

3=>  Automatic Segment Advisor

The Automatic Segment Advisor is an automated maintenance task that is configured to run during all maintenance windows.

The Automatic Segment Advisor does not analyze every database object.
Instead, it examines database statistics, samples segment data, and then selects the following objects to analyze:

Tablespaces that have exceeded a critical or warning space threshold
Segments that have the most activity
Segments that have the highest growth rate

In addition, the Automatic Segment Advisor evaluates tables that are at least 10MB and
that have at least three indexes to determine the amount of space saved if the tables are compressed with the advanced row compression method.

If an object is selected for analysis but the maintenance window expires before the Segment Advisor can process the object, 
the object is included in the next Automatic Segment Advisor run.

You cannot change the set of tablespaces and segments that the Automatic Segment Advisor selects for analysis.
You can, however, enable or disable the Automatic Segment Advisor task, change the times during which the Automatic Segment Advisor is scheduled to run,
or adjust automated maintenance task system resource utilization.

Viewing Automatic Segment Advisor Information

You can query views to display information specific to the Automatic Segment Advisor.
View     Description

DBA_AUTO_SEGADV_SUMMARY    -> Each row of this view summarizes one Automatic Segment Advisor run.
                           Fields include number of tablespaces and segments processed, and number of recommendations made.
DBA_AUTO_SEGADV_CTL     -> Contains control information that the Automatic Segment Advisor uses to select and process segments.
                       Each row contains information on a single object (tablespace or segment), including whether the object has been processed,
                       and if so, the task ID under which it was processed and the reason for selecting it.

4=>  Automatic SQL Tuning Advisor

SQL Tuning Advisor is internal diagnostic software that identifies problematic SQL statements and recommends how to improve statement performance.

When run during database maintenance windows as an automated maintenance task, SQL Tuning Advisor is known as Automatic SQL Tuning Advisor.

SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.

The advisor performs the following types of analysis:

Checks for missing or stale statistics
Builds SQL profiles
Explores whether a different access path can significantly improve performance
Identifies SQL statements that lend themselves to suboptimal plans

The output is in the form of advice or recommendations, along with a rationale for each recommendation and its expected benefit.
The recommendation relates to a collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile.
You can choose to accept the recommendations to complete the tuning of the SQL statements.

5=> SQL Plan Management (SPM) Evolve Advisor

SPM Evolve Advisor is a SQL advisor that evolves plans that have recently been added to the SQL plan baseline.
The advisor simplifies plan evolution by eliminating the requirement to do it manually.

By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window. Optionally, you can configure it to run hourly.

A SQL plan baseline prevents performance regressions caused by suboptimal plans.
If a SQL statement does not have a SQL plan baseline, and if the alternate_plan_baseline parameter is set to AUTO,
then SQM Evolve Advisor can sometimes resolve such performance regressions automatically.
The advisor compares all available plans and chooses the best-performing plan as the baseline.

Whenever it runs in the maintenance window, SPM Evolve Advisor performs the following tasks:

=> Checks AWR for top SQL
         AWR stores the most resource-intensive SQL statements.
         SPM Evolve Advisor searches AWR for statements that are most likely to benefit from SQL plan baselines, and then adds plans for these statements to the baselines.

=> Looks for alternative plans in all available sources 
       By default (alternate_plan_source=AUTO), the automatic task searches all available repositories for plans that are not yet in the SMB plan history.
       The setting for alternate_plan_source is shown in the DBA_ADVISORS_PARAMETERS view.

=> Adds unaccepted plans to the plan history
       These plans are not yet in the SQL plan baseline for any SQL statement.

=> Tests the execution of as many plans as possible during the maintenance window

    For every alternative plan, the database test executes the statement and records the performance statistics.
    The goal is to use a cost-based algorithm to compare the performance of every alternative plan with the plan that the optimizer would otherwise choose.
    Performs either of the following actions, depending on whether the alternative plan performs better than the current plan:

        If performance is better, then SPM Evolve Advisor accepts the plan. The alternative plan is now in the baseline.
        If performance is worse, then the plan remains in the statement history, but not the baseline.

Enabling and Disabling the Automatic SPM Evolve Advisor Task

No separate scheduler client exists for the Automatic SPM Evolve Advisor task.

One client controls both Automatic SQL Tuning Advisor and Automatic SPM Evolve Advisor.
Thus, the same task enables or disables both. You can also disable it using DBMS_SPM.SET_EVOLVE_TASK_PARAMETER.

About Maintenance Windows

A maintenance window is a contiguous time interval during which automated maintenance tasks are run.
Maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP.

How It works

When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window.
Each job is assigned a job name that is generated at run time. 
All automated maintenance task job names begin with ORA$AT.
For example, the job for the Automatic Segment Advisor might be called ORA$AT_SA_SPC_SY_26.
When an automated maintenance task job finishes, it is deleted from the Oracle Scheduler job system. However, the job can still be found in the Scheduler job history.

Predefined Maintenance Windows

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.

Basic Task with Automated Maintenance Tasks

To enable or disable specific maintenance tasks in any subset of maintenance windows, you can use the DBMS_AUTO_TASK_ADMIN PL/SQL package.

Enabling and Disabling Maintenance Tasks for all Maintenance Windows
With a single operation, you can disable or enable a particular automated maintenance task for all maintenance windows.

You can disable a particular automated maintenance task 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.DISABLE;

 Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows

By default, all maintenance tasks run in all predefined maintenance windows. You can disable a maintenance task for a specific window.
The following example disables the Automatic SQL Tuning Advisor from running in the window MONDAY_WINDOW:

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

Automated Maintenance Tasks Database Dictionary Views

DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_OPERATION
DBA_AUTOTASK_SCHEDULE
DBA_AUTOTASK_SCHEDULE_CONTROL
DBA_AUTOTASK_STATUS
DBA_AUTOTASK_TASK
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY