11g: Scheduler Maintenance Tasks or Autotasks [ID 756734.1]

郑全

In this Document
Purpose
Scope and Application
11g: Scheduler Maintenance Tasks or Autotasks
Introduction
Implementation
Summary of changes between 10g and 11g :
Related views :
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 11.1.0.6

Purpose

This bulletin gives more information about the Automated Maintenance Tasks or AUTOTASK jobs which are a new setup in 11g for certain System generated maintenance jobs.

It indentifies some key differences between 10g and 11g related to the Maintenance Tasks.

Scope and Application

This is relevant for Oracle 11g and intended for Oracle DBAs and support analysts.

11g: Scheduler Maintenance Tasks or Autotasks

Introduction

Oracle 11g Database has three predefined automated maintenance tasks:

a? Automatic Optimizer Statistics Collection

Collects optimizer statistics for all schema objects in the database for which there
are no statistics or only stale statistics. The statistics gathered by this task are used
by the SQL query optimizer to improve the performance of SQL execution.

a? Automatic Segment Advisor

Identifies segments that have space available for reclamation, and makes recommendations
on how to defragment those segments. You can also run the Segment Advisor manually
to obtain more up-to-the-minute recommendations or to obtain recommendations on
segments that the Automatic Segment Advisor did not examine for possible space reclamation.

a? Automatic SQL Tuning Advisor

Examines the performance of high-load SQL statements, and makes recommendations
on how to tune those statements. You can configure this advisor to automatically
implement SQL profile recommendations.

Note 466920.1 - 11g New Feature: Health Monitor

Note 755838.1 - New 11g Default Jobs

Implementation

In Oracle10g they were created as separate jobs and visible in DBA_SCHEDULER_JOBS.JOB_NAME.

This has changed in Oracle 11g. The related view is DBA_AUTOTASK_WINDOW_CLIENTS.
The jobs are now only visible in DBA_SCHEDULER_JOBS with their system generated name once they are really executed.

SQL> desc DBA_AUTOTASK_WINDOW_CLIENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
WINDOW_NAME NOT NULL VARCHAR2(30)
WINDOW_NEXT_TIME TIMESTAMP(6) WITH TIME ZONE
WINDOW_ACTIVE VARCHAR2(5)
AUTOTASK_STATUS VARCHAR2(8)
OPTIMIZER_STATS VARCHAR2(8)
SEGMENT_ADVISOR VARCHAR2(8)
SQL_TUNE_ADVISOR VARCHAR2(8)
HEALTH_MONITOR VARCHAR2(8)

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME
------------------------------
WINDOW_NEXT_TIME
---------------------------------------------------------------------------
WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
----- -------- -------- -------- -------- --------
MONDAY_WINDOW
08-DEC-08 10.00.00.000000 PM EUROPE/VIENNA
FALSE ENABLED ENABLED ENABLED ENABLED DISABLED

...

SUNDAY_WINDOW
07-DEC-08 06.00.00.000000 AM EUROPE/VIENNA
FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
7 rows selected.

To enable or disable ALL automated maintenance tasks for all windows, call the ENABLE or DISABLE procedure with no arguments.

SQL> execute DBMS_AUTO_TASK_ADMIN.DISABLE;


To disable a specific maintenance task, use the DISABLE procedure, as follows:

SQL> BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


To enable this specific maintenance task again, use the ENABLE procedure, as follows:

SQL> BEGIN
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


The task names to use for the client_name argument are listed in the DBA_AUTOTASK_CLIENT database dictionary view.

Examples :

auto optimizer stats collection
auto space advisor
sql tuning advisor

Another difference is the predefined Scheduler Windows :

• Oracle10g : WEEKNIGHT_WINDOW and WEEKEND_WINDOW
• Oracle11g : MONDAY_WINDOW .... SUNDAY_WINDOW.

The WEEKNIGHT_WINDOW and WEEKEND_WINDOW are still there for backwards compatibility.

The duration times when the windows are open have changed in 11g. The Monday-Friday are from 10:00PM till 02:00AM and Saturday-Sunday are from 06:00AM till 02:00AM.

You can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure.

Example: The following script moves the WEEKNIGHT_WINDOW to midnight to 8 a.m. every weekday morning (duration of the window is unchanged at 8 hours):

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
   'WEEKNIGHT_WINDOW', 
   'repeat_interval',
   'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

Each weekday window also has a pre-defined Resource Plan called DEFAULT_MAINTENANCE_PLAN, which will become active once the related window opens. This is another difference between 10g and 11g.

SQL> select window_name, resource_plan from dba_scheduler_windows;

WINDOW_NAME RESOURCE_PLAN
------------------------------ ------------------------------
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

9 rows selected.

SQL> select * from dba_rsrc_plans where plan='DEFAULT_MAINTENANCE_PLAN'
PLAN_ID PLAN NUM_PLAN_DIRECTIVES
---------- ------------------------------ -------------------
CPU_METHOD MGMT_METHOD
------------------------------ ------------------------------
ACTIVE_SESS_POOL_MTH PARALLEL_DEGREE_LIMIT_MTH
------------------------------ ------------------------------
QUEUEING_MTH SUB
------------------------------ ---
COMMENTS
--------------------------------------------------------------------------------
STATUS MAN
------------------------------ ---
11187 DEFAULT_MAINTENANCE_PLAN 4
EMPHASIS EMPHASIS
ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE
FIFO_TIMEOUT NO
Default plan for maintenance windows that prioritizes SYS_GROUP operations and a
llocates the remaining 5% to diagnostic operations and 25% to automated maintena
nce operations.
YES

SQL> select * from DBA_RSRC_PLAN_DIRECTIVES where plan='DEFAULT_MAINTENANCE_PLAN';

PLAN GROUP_OR_SUBPLAN TYPE
------------------------------ ------------------------------ --------------
CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7
---------- ---------- ---------- ---------- ---------- ---------- ----------
CPU_P8 MGMT_P1 MGMT_P2 MGMT_P3 MGMT_P4 MGMT_P5 MGMT_P6
---------- ---------- ---------- ---------- ---------- ---------- ----------
MGMT_P7 MGMT_P8 ACTIVE_SESS_POOL_P1 QUEUEING_P1 PARALLEL_DEGREE_LIMIT_P1
---------- ---------- ------------------- ----------- ------------------------
SWITCH_GROUP SWITC SWITCH_TIME SWITCH_IO_MEGABYTES
------------------------------ ----- ----------- -------------------
SWITCH_IO_REQS SWITC MAX_EST_EXEC_TIME UNDO_POOL MAX_IDLE_TIME
-------------- ----- ----------------- ---------- -------------
MAX_IDLE_BLOCKER_TIME SWITCH_TIME_IN_CALL
--------------------- -------------------
COMMENTS
--------------------------------------------------------------------------------
STATUS MAN
------------------------------ ---
DEFAULT_MAINTENANCE_PLAN SYS_GROUP CONSUMER_GROUP
100 0 0 0 0 0 0
0 100 0 0 0 0 0
0 0
FALSE
FALSE

Directive for system operations
NO

DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS CONSUMER_GROUP
0 70 0 0 0 0 0
0 0 70 0 0 0 0
0 0
FALSE
FALSE

Directive for all other operations
NO

DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN PLAN
0 25 0 0 0 0 0
0 0 25 0 0 0 0
0 0
FALSE
FALSE

Directive for automated maintenance tasks
NO

DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP
0 5 0 0 0 0 0
0 0 5 0 0 0 0
0 0
FALSE
FALSE

Directive for automated diagnostic tasks
NO


Summary of changes between 10g and 11g :

Subject	10g	11g
Jobs	Separate jobs in DBA_SCHEDULER_JOBS	AUTOTASKS has a naming prefix 'ORA$AT' and are only visible as a job once executed
Maintenance Window	2 windows, WEEKNIGHT and WEEKEND	Each day has its own window
Resource manager	Not enabled per default	Pre-defined Resource Plan for every weekday window

Related views :

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

References

NOTE:466920.1 - 11g New Feature: Health monitor
NOTE:755838.1 - New 11g Default Jobs
11g Database Administrator's Guide : Chapter 24
NOTE:858852.1 - DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection