8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Automated Database Maintenance Task Management in Oracle Database 11g Release 1

Introduction

This article is a brief overview of the management of automated database maintenance tasks in Oracle database 11g. I've tried to keep this article quite light for two of reasons:

  1. I'm sure a lot of people will never touch these settings. Having said that, if most of your processing is done at night, having the maintenance windows opening at night is not a good idea and you should consider altering them.
  2. The basic management tasks are quite self contained, but beyond that the discussion moves on to the scheduler and the resource manager, which is a bit beyond the scope of the article. I've put links to articles on those subjects at the bottom of this article.

Oracle 11g includes three automated database maintenance tasks:

These tasks run during maintenance windows scheduled to open over night. Configuration of the maintenance tasks, their schedules and resource usage is possible using Enterprise Manager or PL/SQL APIs.

The "Automated Maintenance Tasks" screen displays the maintenance window for each task (Server > Automated Maintenance Tasks (link under Scheduler section)). Click the "Configure" button to navigate to the configuration screens.

Automated Maintenance Tasks

Basic Task Configuration

The "Automated Maintenance Tasks Configuration" screen is the stating point for all maintenance task configuration.

Automated Maintenance Tasks Configuration

The "Global Status" switch allows you to enable or disable all automated tasks for all maintenance windows.

Global Status

The DISABLE and ENABLE procedures of the DBMS_AUTO_TASK_ADMIN package achieve the same result if they are called with no parameters.

EXEC DBMS_AUTO_TASK_ADMIN.disable;
EXEC DBMS_AUTO_TASK_ADMIN.enable;

The "Task Settings" section allows you to enable or disable individual tasks from all maintenance windows.

Task Settings

This can be done using the DBMS_AUTO_TASK_ADMIN package by specifying the task name in the CLIENT_NAME parameter of the DISABLE and ENABLE procedures.

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.enable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

The "Maintenance Window Group Assignment" section provides the most granular level of control. It allows tasks to be removed or added to individual maintenance windows.

Maintenance Window Group Assignment

This can be done using the DBMS_AUTO_TASK_ADMIN package by specifying the CLIENT_NAME and WINDOW_NAME parameters of the DISABLE and ENABLE procedures.

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => 'MONDAY_WINDOW');

  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => 'MONDAY_WINDOW');

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

Task Parameter Configuration

The "Task Settings" section of the "Automated Maintenance Tasks Configuration" screen includes two "Configure" buttons.

Task Settings

The "Configure" button next to the "Optimizer Statistics Gathering" task takes you to the "Global Statistics Gathering Options" screen.

Global Statistics Gathering Options

With the exception of the history retention, these settings can all be altered with the SET_GLOBAL_PREFS procedure in the DBMS_STATS package. Click here for a list of the possible parameter values.

EXEC DBMS_STATS.alter_stats_history_retention(90);
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '5');

The "Configure" button next to the "Automatic SQL Tuning" task takes you to the "Automatic SQL Tuning Settings" screen.

Automatic SQL Tuning Settings

These settings can all be altered with the SET_TUNING_TASK_PARAMETER procedure in the DBMS_SQLTUNE package.

BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 1200);
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'ACCEPT_SQL_PROFILES', 'FALSE');
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_SQL_PROFILES_PER_EXEC', 20);
  DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_AUTO_SQL_PROFILES', 10000);
END;
/

Maintenance Window Configuration

The maintenance windows are defined using the Oracle Scheduler. Oracle provide a separate active maintenance window for each day, with all windows collected into a window group called "MAINTENANCE_WINDOW_GROUP". Clicking the "Edit Window Group" button on the "Automated Maintenance Tasks Configuration" screen allows you to view and modify the window group.

Edit Window Group

The whole window group can be enabled or disabled using this screen, or using the ENABLE and DISABLE procedures in the DBMS_SCHEDULER package.

BEGIN
  DBMS_SCHEDULER.disable(
    name  => 'SYS.MAINTENANCE_WINDOW_GROUP',
    force => TRUE);

  DBMS_SCHEDULER.enable(
    name  => 'SYS.MAINTENANCE_WINDOW_GROUP');
END;
/

Clicking on the window name on this screen, or in the "Automated Maintenance Tasks Configuration" screen, takes you to the "View Windows" screen, which gives you a summary of the window configuration.

View Window

Click the "Edit" button to alter the window definition.

Edit Window

These configuration changes can be performed using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package. Click here for a full list of window attributes.

BEGIN
  DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);

  DBMS_SCHEDULER.set_attribute(
    name      => 'SYS.MONDAY_WINDOW',
    attribute => 'DURATION',
    value     => numtodsinterval(180, 'minute'));

  DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');
END;
/

If you wish to create additional maintenance windows, make sure they are assigned to the "MAINTENANCE_WINDOW_GROUP" window group.

A full discussion of the scheduler is beyond the scope of this article, but relevant articles are listed below.

Resource Plan Configuration

The "Edit Window" screen includes "View Resource Plan" and "Create Resource Plan" buttons. These buttons take you to the resource manager maintenance screens. All maintenance windows are assigned to the "DEFAULT_MAINTENANCE_PLAN" resource plan by default.

View Resource Plan

If necessary, you can edit this plan, or create a new plan.

Edit Resource Plan

If you create a new resource plan, you need assign it to the relevant maintenance windows in the "Edit Window" screen, or using the DBMS_SCHEDULER package.

BEGIN
  DBMS_SCHEDULER.disable(name => 'SYS.MONDAY_WINDOW', force => TRUE);

  DBMS_SCHEDULER.set_attribute(
    name      => 'SYS.MONDAY_WINDOW',
    attribute => 'RESOURCE_PLAN',
    value     => 'MY_NEW_PLAN');

  DBMS_SCHEDULER.enable(name=>'SYS.MONDAY_WINDOW');
END;
/

To blank the resource plan issue the following command. Thanks to "Geert" in the comments for pointing out this fails when using NULL, but works with ''.

BEGIN
  DBMS_SCHEDULER.set_attribute(
    name      => 'SYS.MONDAY_WINDOW',
    attribute => 'RESOURCE_PLAN',
    value     => ''); 
END;
/

A full discussion of the resource manager is beyond the scope of this article, but relevant articles are listed below.

Relevant Views

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

In addition, you may need to refer to the scheduler and resource manager views.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.