8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Automated Database Maintenance Task Management in Oracle Database 11g Release 1
- Introduction
- Basic Task Configuration
- Task Parameter Configuration
- Maintenance Window Configuration
- Resource Plan Configuration
- Relevant Views
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:
- 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.
- 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:
- Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects (more info). The task name is 'auto optimizer stats collection'.
- Automatic Segment Advisor - Identifies segments that could be reorganized to save space (more info). The task name is 'auto space advisor'.
- Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL (more info). The task name is 'sql tuning advisor'.
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.
Basic Task Configuration
The "Automated Maintenance Tasks Configuration" screen is the stating point for all maintenance task configuration.
The "Global Status" switch allows you to enable or disable all automated tasks for all maintenance windows.
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.
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.
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.
The "Configure" button next to the "Optimizer Statistics Gathering" task takes you to the "Global Statistics Gathering Options" screen.
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.
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.
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.
Click the "Edit" button to alter the window definition.
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.
If necessary, you can edit this plan, or create a new 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:
- DBA_AUTOTASK_CLIENT
- DBA_AUTOTASK_CLIENT_HISTORY
- DBA_AUTOTASK_CLIENT_JOB
- DBA_AUTOTASK_JOB_HISTORY
- DBA_AUTOTASK_OPERATION
- DBA_AUTOTASK_SCHEDULE
- DBA_AUTOTASK_TASK
- DBA_AUTOTASK_WINDOW_CLIENTS
- DBA_AUTOTASK_WINDOW_HISTORY
In addition, you may need to refer to the scheduler and resource manager views.
For more information see:
- Managing Automated Database Maintenance Tasks
- DBMS_AUTO_TASK_ADMIN
- DBMS_STATS
- DBMS_SQLTUNE
- DBMS_SCHEDULER
- Scheduler in Oracle Database 10g
- Scheduler Enhancements in Oracle 10g Database Release 2
- Resource Manager in Oracle 8i
- Resource Manager Enhancements in Oracle 9i
- Resource Manager Enhancements in Oracle Database 10g
Hope this helps. Regards Tim...