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:
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.
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; /
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; /
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.
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.
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 normal view: https://oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1