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.2Information 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â– Automatic Segment Advisor
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.
Identifies segments that have space available for reclamation, and makes recommendationsâ– Automatic SQL Tuning Advisor
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.
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 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.
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.DBMS_SCHEDULER.SET_ATTRIBUTE
procedure.
Example: T
he 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 monitorNOTE: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
相关内容 产品
|