In this Document

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_HISTORY

Refer 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

ITPub博客

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