Oracle 11g Automated Maintenance Tasks(原创)

Overview
You’re familiar with the concept of automated maintenance tasks from the Oracle Database 10g release. These are jobs that are run automatically by the database to perform maintenance operations. In Oracle Database 10g, you had two automatic maintenance tasks: the Automatic Optimizer Statistics collection and the Automatic Segment Advisor. In Oracle Database 11g, there is a third automatic  maintenance task named Automatic SQL Tuning Advisor. The Automatic SQL Tuning Advisor reviews all high resource consuming SQL statements in the database and provides recommendations to tune them. If you want, you can configure the database so it automatically implements some types of recommendations, such as SQL profiles.This artical will revolve around Automatic Sql Tuning Task to introduce the Automated Maintenance Task.

The Automatic SQL Tuning Advisor runs during the default system maintenance window on a nightly basis, just as the other two automated maintenance tasks do. A maintenance window is an Oracle Scheduler window that’s part of the MAINTENANCE_WINDOW_GROUP. You choose low system load time interval periods for the maintenance windows. A Scheduler resource plan specifies how the database will allocate resources during the duration of a window. When a Scheduler window opens, the database automatically enables the resource plan associated with that window. 

Predefi ned Maintenance Windows

In Oracle Database 10g, you had two maintenance windows: weeknight_window and weekend_window. In Oracle Database 11g, there are seven predefined daily maintenance windows, one for each day of the week. Here are the predefined maintenance windows and their descriptions: 

 

• MONDAY_WINDOW               Starts 10 P.M. on Monday ends at 2 A.M.
• TUESDAY_WINDOW              Starts 10 P.M. on Tuesday ends at 2 A.M.
• WEDNESDAY_WINDOW        Starts 10 P.M. on Wednesday ends at 2 A.M.
• THURSDAY_WINDOW            Starts 10 P.M. on Thursday ends at 2 A.M.
• FRIDAY_WINDOW                  Starts 10 P.M. on Friday ends at 2 A.M.
• SATURDAY_WINDOW            Starts 6 A.M on Saturday ends at 2.A.M
• SUNDAY_WINDOW                Starts 6 A.M. on Sunday ends at 2 A.M.

Note that the first five windows that run during the weekdays are open for 4 hours and the two weekend maintenance windows are open for 20 hours. By default, all seven daily windows belong to the MAINTENANCE_WINDOW_GROUP group. You can change the time and duration of the daily maintenance windows, create new maintenance windows, or disable or remove the default maintenance windows. 

Managing the Automatic Maintenance Tasks

In Oracle Database 10g, you had to use the DBMS_SCHEDULER package to enable and disable the automatic maintenance tasks. The ENABLE procedure lets you enable an automatic maintenance job such as the automatic statistics collection job, and the DISABLE procedure lets you disable it, if you wanted to manually collect the optimizer statistics. In Oracle Database 11g, use the new DBMS_AUTO_TASK_ ADMIN package to manage the automatic maintenance tasks. You can also use the Enterprise Manager to access the controls for the automatic maintenance tasks. The DBMS_AUTO_TASK_ADMIN package provides a more fine-grained management capability to control the operation of the automatic maintenance tasks. For example, the DBMS_SCHEDULER package only lets you enable or disable an automatic task. With the new DBMS_AUTO_TASK_ADMIN package, you can now disable a task only in selected maintenance windows instead of completely disabling the entire task. Before you start looking into the management of the automatic maintenance tasks, it’s a good idea to get familiar with two new views that provide information you might need to manage the tasks: the DBA_AUTOTASK_CLIENT view and the DBA_AUTOTASK_OPERATION view. The two views contain several identical columns. The DBA_AUTOTASK_CLIENT view shows data for all three automated tasks over a 1-day and a 30-day period. The following query shows details about the automatic maintenance tasks: 

SQL> select client_name, status,
  2  attributes, window_group,service_name
  3  from dba_autotask_client;
CLIENT_NAME            STATUS     ATTRIBUTES
--------------------  --------    ------------------------------
auto optimizer        ENABLED     ON BY DEFAULT, VOLATILE,
statistics collection             SAFE TO KILL
auto space advisor    ENABLED     ON BY DEFAULT, VOLATILE,
                                  SAFE TO KILL
sql tuning advisor    ENABLED     ONCE PER WINDOW,ON BY DEFAULT,
                                  VOLATILE, SAFE TO KILL
You can see that all three of the automatic maintenance tasks are enabled. When the maintenance window opens, Oracle Scheduler automatically creates the automatic maintenance jobs and runs them. If the maintenance window is long, Oracle restarts the automatic optimizer statistics collection and the automatic segment advisor jobs every four hours. However, the automatic SQL advisor job runs only once per maintenance window, as evidenced by the ONCE PER WINDOW attribute for that job. The attributes column shows only ON BY DEFAULT as the value for the other two automated maintenance tasks. Each of the automatic maintenance tasks is called a client and is given a client name. The actual Scheduler job associated with each of the three clients is called an operation and is given an operation name. The following query on the DBA_AUTO_TASK_OPERATION view shows the operation names:

SQL> select client_name, operation_name from
dba_autotask_operation;
CLIENT_NAME                OPERATION_NAME
----------------------     -------------------------
auto optimizer             auto optimizer stats job
stats collection
auto space advisor         auto space advisor job
sql tuning advisor         automatic sql tuning task
Enabling a Maintenance task

Use the DBMS_AUTO_ADMIN.ENABLE procedure to enable a client, operation, target type, or individual target that you previously disabled. Before you can do this, you must first query the DBA_AUTOTASK_CLIENT and the DBA_AUTOTASK_ OPERATION views to find the values for the client_name and operation_ name attributes of the procedure.

SQL> begin
  2  dbms_auto_task_admin.enable
  3  (client_name  => 'sql tuning advisor',
  4  operation     => 'automatic sql tuning task',
  5  window_name   => 'monday_window');
  6* end;
PL/SQL procedure successfully completed.

Disabling a Maintenance Task

By default, all three maintenance jobs will run in every maintenance window. You can use the DBMS_AUTO_ADMIN.DISABLE procedure to disable a client or operation for a specific window, as shown here:

SQL> begin
  2     dbms_auto_task_admin.disable(
  3          client_name => 'sql tuning advisor',
  4          operation   => 'automatic sql tuning task',
  5          window_name => 'monday_window');
  6  end;
  7  /
PL/SQL procedure successfully completed.

Maintenance Window Configuration

Before you configure the window attributes,you need to get the current window attributes by querying DBA_SCHEDULER_WINDOWS view.

SQL> SELECT WINDOW_NAME, DURATION
  2  FROM DBA_SCHEDULER_WINDOWS
  3  WHERE WINDOW_NAME = 'TUESDAY_WINDOW';
WINDOW_NAME      DURATION
---------------- --------------------
TUESDAY_WINDOW   +000 03:00:00

To configure window attributes,you can use following command:

SQL> BEGIN
  2      DBMS_SCHEDULER.set_attribute(
  3      name    =>  'TUESDAY_WINDOW',
  4  attribute => 'DURATION',
  5  value   => numtodsinterval(120,'minute'));
  6  END;
  7  /
PL/SQL procedure successfully completed.

SQL> SELECT WINDOW_NAME, DURATION
  2   FROM DBA_SCHEDULER_WINDOWS
  3   WHERE WINDOW_NAME = 'TUESDAY_WINDOW';
WINDOW_NAME      DURATION
---------------- --------------------
TUESDAY_WINDOW   +000 02:00:00

The other window attributes you can find from following diagram.

Name	Description
comments	An optional comment about the window.
duration	The duration of the window.
end_date	The date after which the window will no longer open. If this is set, schedule_name must be NULL.
repeat_interval	A string using the calendaring syntax. PL/SQL date functions are not allowed. If this is set, schedule_name must be NULL. See "Calendaring Syntax" for more information.
resource_plan	The resource plan to be associated with a window. When the window opens, the system will switch to this resource plan. When the window closes, the original resource plan will be restored. If a resource plan has been made active with the force option, no resource plan switch will occur. Only one resource plan can be associated with a window. It may be NULL or the empty string (""). When it is NULL, the resource plan that is in effect when the window opens stays in effect for the duration of the window. When it is the empty string, the resource manager is disabled for the duration of the window.
schedule_name	The name of a schedule to use with this window. If this is set, start_date, end_date, and repeat_interval must all be NULL.
start_date	The next date and time on which this window is scheduled to open. If this is set, schedule_name must be NULL.
window_priority	The priority of the window. Must be one of 'LOW' (default) or 'HIGH'.

 

Implementing Automatic Maintenance Task

The database doesn’t assign any permanent Scheduler jobs to the three automated maintenance tasks. You therefore can’t manage the jobs with the usual DBMS_ SCHEDULER package. Use the new DBMS_AUTO_TASK_ADMIN package instead to manage the automated maintenance tasks. The new background process, Autotask Background Process (ABP), implements the automated maintenance tasks. The ABP maintains a history of all automated maintenance task executions in the repository that it maintains in the SYSAUX tablespace. Another background process, MMON, spawns (usually when a maintenance window opens), monitors, and restarts the ABP process. The ABP is in charge of converting tasks into Scheduler jobs. The ABP creates a task list for each maintenance job and assigns them a priority. There are three levels of job priorities: urgent, high, and medium. The ABP creates the urgent priority jobs first, after which it creates the high priority and the medium priority jobs. Various Scheduler job classes are also created, in order to map a task’s priority consumer group to the corresponding job class. The ABP is in charge of assigning the jobs to the job classes. The job classes map the individual jobs to a consumer group, based on the job priority. The ABP stores its data in the SYSAUX tablespace. You can view the ABP repository by querying the DBA_AUTOTASK_TASK view.  

Configuring Resource Allocation for Automatic Tasks

You can control the percentage of resources allocated to the maintenance tasks during a given maintenance window. The default resource plan for each predefined maintenance window is the DEFAULT_MAINTENANCE_PLAN. When a maintenance window opens, the DEFAULT_MAINTENANCE_PLAN is activated to control the amount of CPU used by the various automatic maintenance tasks. The three default maintenance tasks run under the ORA$AUTOTASK_SUB_PLAN, which is a subplan of the DEFAULT_MAINTENANCE_PLAN, with all three plans sharing the resources equally. ORA$AUTOTASK_SUB_PLAN gets 25 percent of the resources at the priority level 2. The consumer group SYS_GROUP takes priority in the DEFAULT_MAINTENANCE_PLAN resource plan, getting 100 percent of the level 1 resources in the DEFAULT_MAINTENANCE_PLAN. If you want to change the resource allocation for the automatic tasks in a specific window, you must change the resource allocation to the subplan ORA$AUTOTASK_SUB_PLAN in the resource plan for that window.

Priorities for the various tasks that run during the maintenance window (three tasks altogether) are determined by assigning different consumer groups to the DEFAULT_ MAINTENANCE_PLAN. For example, the new Automatic SQL Tuning task is assigned to the ORA$AUTOTASK_SQL_GROUP consumer group. The Optimizer Statistics Gathering task is part of the ORA$AUTOTASK_STATS_GROUP, and the Segment Advisor task belongs to the ORA$AUTOTASK_SPACE_GROUP.

 

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

                http://www.oracle-base.com/articles/11g/awr-baseline-enhancements-11gr1.php

                http://www.oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1.php

                http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHDAIIH

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:czmcj@163.com