Automatic database maintenance task management in Oracle 11g R1 (1)

BKJIA exclusive Translation]

This article provides a general introduction to the automatic database maintenance task management in Oracle 11g. The article is concise for two reasons:

1. Most people have not touched these settings. Even so, if you are processing maintenance tasks at night and opening a maintenance window at night is not a good idea, you may want to change it all.

2. Basic management tasks are self-controlled. However, in addition to the discussion on migration to the scheduler and resource manager, other tasks are beyond the scope of this article. I have attached a reference link at the end of this article.
Oracle 11g includes the following automatic database maintenance tasks:

Automatic optimizer statistics collection: Collect obsolete or missing statistics for all solution objects. The task name is "optimizer stats collection"

Automatic Segmentation Advisor: Identifies the segments to be reorganized to save space. The task name is "auto space advisor"

Automatic SQL adjustment Consultant: Identify and try to adjust the High-load SQL, the task name is "SQL tuning advisor"

These tasks are executed when the maintenance window is opened at night. You can use Enterprise Manager EM) or PL/SQL API to configure maintenance tasks, as well as their scheduling information and resource usage.

The "automatic maintenance task" screen shows the maintenance window for each task. The server worker automatic maintenance task link is under the "scheduler"). Click the "configuration" button to go to the configuration page.

Basic Task Configuration

The "automatic maintenance task configuration" screen is the entry to all maintenance task configurations.

 

The Global Status switch allows you to enable or disable the maintenance window for all automatic tasks.

The ENABLE and DISABLE stored procedures of the DBMS_AUTO_TASK_ADMIN package can achieve the same effect without any parameters ):

EXEC DBMS_AUTO_TASK_ADMIN.disable;EXEC DBMS_AUTO_TASK_ADMIN.enable;

The "task Settings" section allows you to enable or disable a maintenance task window separately.

 

Similarly, you can use the ENABLE and DISABLE stored procedures in the DBMS_AUTO_TASK_ADMIN package to specify the corresponding task name in the CLIENT_NAME parameter:

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 allocation Section provides fine-grained control that allows you to remove or add tasks in a single maintenance window.

Similarly, you can use the ENABLE and DISABLE stored procedures in the DBMS_AUTO_TASK_ADMIN package. You only need to specify the values for the CLIENT_NAME and WINDOW_NAME parameters:

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;/

#P#

Task parameter configuration

The "task configuration" section under "automatic maintenance task configuration" contains two "configuration" buttons.

Click "Configure" after "statistics collection by optimizer" to display the "global statistics collection options" screen.

 

You can use the SET_GLOBAL_PREFS stored procedure in the DBMS_STATS package to modify these settings. For more information, see

Bytes.

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

Click the "configuration" button after "automatic SQL adjustment task" to display the "Automatic SQL adjustment Settings" screen.

These settings can also be modified using the SET_TUNING_TASK_PARAMETER stored 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

Oracle uses the Oracle scheduler to define the maintenance window. Each day, Oracle provides an independent active maintenance window. All the windows are collected into a window group called MAINTENANCE_WINDOW_GROUP, click the "edit window group" button on the "Automatic maintenance task configuration" screen to view the details of your window group or modify the window group.

Use the functions provided on this screen to ENABLE/DISABLE all window groups, or use the ENABLE and DISABLE stored 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;/

Click the window name on this screen or the window name on the "Automatic maintenance task configuration" screen to display the "View window" screen, showing the summary of the window configuration.