oracle11g: Scheduler Maintenance Tasks or Autotasks (Doc ID 756734.1)

来源：这里教程网时间：2026-03-03 17:37:25 作者：

In this Document

Purpose

Scope

Details

Introduction

Implementation

Summary of changes between 10g and 11g :

Related views :

References

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Information in this document applies to any platform. Oracle Server Enterprise Edition - Version: 11.1.0.6

PURPOSE

This bulletin provides information about the Automated Maintenance Tasks or AUTOTASK jobs which are a new setup in 11g for certain System generated maintenance jobs. It identifies some key differences between 10g and 11g related to the Maintenance Tasks Maintenance Windows.

SCOPE

This is relevant for Oracle 10g, 11g, 12c and intended for Oracle DBAs and support analysts.

DETAILS

Introduction

Oracle 11g Database has three predefined automated maintenance tasks: â 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.

â 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.

â 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_ATTRIBUTEprocedure.
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_HISTORYRefer to the document  
Doc ID 1300313.1 for details on how to create a a specific Maintenance Window to allow a particular task to run in a user window instead of one of the default Maintenance Windows.
REFERENCES

NOTE:755838.1
 - New 11g Default Jobs

NOTE:2092353.1
 - How to Limit CPU Allocation for Auto Maintenance Tasks

NOTE:2096673.1
 - IF: Default Maintenance Jobs in 10g Databases

NOTE:858852.1
 - DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection

NOTE:466920.1
 - Health Monitor

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