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

最新推荐文章于 2025-12-04 07:03:54 发布

转载最新推荐文章于 2025-12-04 07:03:54 发布 · 1.3k 阅读

文章标签：

#statistics #database #jobs #oracle #oracle10g #windows

Oracle JOB 专栏收录该内容

5 篇文章

订阅专栏

This document provides information about the Automated Maintenance Tasks or AUTOTASK jobs in Oracle 11g, focusing on their introduction, implementation, and key differences compared to Oracle 10g. It also discusses the new setup for system-generated maintenance jobs and the changes in visibility and execution of these tasks.

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

		修改时间 14-SEP-2011 类型 BULLETIN 状态 PUBLISHED

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:

â– 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_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

显示相关信息 相关内容