Oracle 11g 数据库有三个预定义自动维护任务：
Automatic Optimizer Statistics Collection（自动优化器统计信息收集）
收集数据库中所有无统计信息或仅有过时统计信息的 Schema 对象的 Optimizer（优化器）统计信息。SQL query optimizer（SQL 查询优化器）使用此任务收集的统计信息提高 SQL 执行的性能。
Automatic Segment Advisor（自动段指导）
识别有可用回收空间的段，并提出如何消除这些段中的碎片的建议。您也可以手动运行 Segment Advisor 获取更多最新建议，或获取 Automatic Segment Advisor 没有检查到的那些有可能做空间回收的段的建议。
Automatic SQL Tuning Advisor（自动 SQL 优化指导）
检查高负载 SQL 语句的性能，并提出如何优化这些语句的建议。您可以配置此指导，自动应用建议的SQL profile。
Note 755838.1
实施
它们在 Oracle10g 中为独立作业，且在 DBA_SCHEDULER_JOBS.JOB_NAME 中可见。
这在 Oracle11g 中有所更改。相关视图为 DBA_AUTOTASK_WINDOW_CLIENTS。
现在，这些作业只有当被真正执行时，才在 DBA_SCHEDULER_JOBS 中可见，且名称为系统生成的名称。
SQL>
WINDOW_NAME
------------------------------
WINDOW_NEXT_TIME
---------------------------------------------------------------------------
WINDO
-----
MONDAY_WINDOW
08-DEC-08
FALSE
...
SUNDAY_WINDOW
07-DEC-08
FALSE
7
要启用或禁用所有窗口的所有自动维护任务，在不使用任何参数的情况下调用 ENABLE 或 DISABLE 程序。
要禁用特定维护任务，使用 DISABLE 程序，如下所示：
要再次启用该特定维护任务，使用 ENABLE 程序，如下所示：
client_name 参数使用的任务名称列在 DBA_AUTOTASK_CLIENT 数据库字典视图中。
auto optimizer stats collection
auto space advisor
sql tuning advisor
另一个差异是预定义调度程序窗口：
-
Oracle10g : WEEKNIGHT_WINDOW and WEEKEND_WINDOW -
Oracle11g : MONDAY_WINDOW .... SUNDAY_WINDOW.
为了向下兼容，WEEKNIGHT_WINDOW 和 WEEKEND_WINDOW 仍然存在。
窗口打开持续的时间在 11g 中有所更改。星期一到星期五从晚上 10 点到凌晨 2 点，星期六到星期日从上午 6 点到凌晨 2 点。
'WEEKNIGHT_WINDOW',
'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');
每个窗口还有一个名为 DEFAULT_MAINTENANCE_PLAN 的预定义资源计划，一旦打开相关窗口，该计划将激活。这是 10g 和 11g 之间的另一个差异。
WINDOW_NAME
------------------------------ ------------------------------
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW
WEEKNIGHT_WINDOW
WEEKEND_WINDOW
9 rows selected.
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
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
10g 与 11g 之间变化的摘要：
| 主题 | 10g | 11g |
|---|---|---|
| 作业 | 在 DBA_SCHEDULER_JOBS 中独立作业 | AUTOTASKS 有命名前缀“ORA$AT”，且只有作业执行后才可见 |
| 维护窗口 | 2 个窗口：WEEKNIGHT 和 WEEKEND | 每天有不同的窗口 |
| Resource manager | 默认不启用 | 每个窗口都有预定义资源计划 |
| | | |
相关视图：
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
NOTE:755838.1
NOTE:858852.1
SELECT * FROM dba_tab_stats_history 查看表统计时间
FROM DBA_AUTOTASK_JOB_HISTORY
WHERE JOB_NAME like 'ORA$AT_OS%';
@?/rdbms/admin/catnomwn.sql -- this drops the maintenance window, it will give some errors that can be ignored.
-- Drop the windows manually:
execute dbms_scheduler.drop_window('MONDAY_WINDOW');
execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
execute dbms_scheduler.drop_window('SUNDAY_WINDOW');
@
?/rdbms/admin/catmwin.sql -- this recreates it
column WINDOW_NAME format a16
column WINDOW_NEXT_TIME format a38
column WINDOW_ACTIVE format a5
column OPTIMIZER_STATS format a10
column comments format a38
select window_group_name, enabled, number_of_windows, next_start_date, comments from dba_scheduler_window_groups;
1. Statistics_level must NOT BE = basic. Should be =TYPICAL otherwise stats not collected.
show parameter statistics_level
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
statistics_level string TYPICAL
2. Are the autotasks (3 of them) listed and ENABLED?
set pages 1000
select client_name, status, window_group from dba_autotask_client;
3. Check the Windows and WINDOW_NEXT_TIME for day returned.
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
4. This checks history of the optimizer collection.
col job_name format a23
col job_start_time format a40
col job_duration format a14
select job_name, job_start_time, job_duration
from DBA_AUTOTASK_JOB_HISTORY
where client_name like 'auto optimizer stats collection'
order by job_start_time;
5) Here is command to enable autotask.
--> execute DBMS_AUTO_TASK_ADMIN.ENABLE;
Then AUTOTASK_STATUS in DBA_AUTOTASK_WINDOW_CLIENTS view will show ENABLED for all Windows.
6) To manually run, just to ensure at least 1 run:
SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
7) Recheck history using the query in #4.
6) If ever there is a large data load (ie - HR2HR or other import), manually run the job (step 4) when you are satisfied that the data load is finished and successful.
In 11g, the automated maintenance task that collects statistics is called 'auto optimizer stats collection'. You can determine its status by selecting fromDBA_AUTOTASK_CLIENT:
STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection'
/
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
The DBA_AUTOTASK_CLIENT view also displays frequency information about current and past automated maintenance tasks in columns MEAN_INCOMING_TASKS_7_DAYS and MEAN_INCOMING_TASKS_30_DAYS so you can see this historical information using a query like:
STATUS ,
MEAN_INCOMING_TASKS_7_DAYS,
MEAN_INCOMING_TASKS_30_DAYS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection'
/
-
How to enable auto stats collection?
If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
-
How to disable the auto stats collection?
In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
For details of the other columns in DBA_AUTOTASK_CLIENT,see:
11g Release 2 (11.2)
Part Number E25513-01
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_3084.htm
10g
The following is the 'old method' for checking the current and past automated maintenance tasks:
j.program_name ,
j.schedule_name,
j.job_class ,
p.enabled
FROM dba_scheduler_programs p,
dba_scheduler_jobs j
WHERE p.program_name=j.program_name;
JOB_NAME
------------------------------
PROGRAM_NAME
--------------------------------------------------------------------------------
SCHEDULE_NAME
--------------------------------------------------------------------------------
JOB_CLASS ENABL
------------------------------ -----
..
GATHER_STATS_JOB
GATHER_STATS_PROG
MAINTENANCE_WINDOW_GROUP
AUTO_TASKS_JOB_CLASS TRUE
-
How do you disable the GATHER_STATS_JOB for 10g?
The most direct approach is to disable the GATHER_STATS_JOB as follows:
SQL> exec sys.dbms_scheduler.disable ('GATHER_STATS_JOB'); -
How do you enable the GATHER_STATS_JOB for 10g?
This is enabled by default. If you have disabled it, then you can re-enable it as
SQL> exec sys.dbms_scheduler.enable (€"SYS"."GATHER_STATS_JOB");
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning and Real Application Testing options
SQL> SELECT job_name ,
job_type ,
program_name ,
schedule_name,
job_class
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_JOB';
no rows selected
周一到周五是晚上10点开始到2点结束
周末是早上六点，持续20个小时。
1、查看自动收集任务及状态
select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';
2、停止自动收集任务
SQL> BEGIN 2 DBMS_AUTO_TASK_ADMIN.DISABLE( 3 client_name => 'auto optimizer stats collection', 4 operation => NULL, window_name => NULL); 5 END; 6 /PL/SQL procedure successfully completed.SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection DISABLED
但是此时再查询DBA_ATUOTASK_TASK视图时，显示该任务状态还是ENABLED
SQL> select client_name,status from dba_autotask_task where client_name='auto optimizer stats collection';CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED
Oracle给出的解释是在现在的版本中（11.1 to 11.2）一个client对应一个task，但是在将来的版本中会出现多个client会对应一个task，所以一个client被disabled了，不会改变task的状态。[ID 858852.1]
3、启动自动收集任务
SQL> BEGIN 2 DBMS_AUTO_TASK_ADMIN.ENABLE( 3 client_name => 'auto optimizer stats collection', 4 operation => NULL, window_name => NULL); 5 END; 6 /PL/SQL procedure successfully completed.SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED
4、查看自动收集任务历史执行状态
SQL> SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed 2 FROM dba_autotask_client_history 3 WHERE client_name like '%stats%';CLIENT_NAME WINDOW_NAME JOBS_CREATED JOBS_STARTED JOBS_COMPLETED ---------------------------------------- ------------------------------ ------------ ------------ -------------- auto optimizer stats collection SATURDAY_WINDOW 5 5 5 auto optimizer stats collection SUNDAY_WINDOW 6 6 6
通过时间窗口名称可以看出是周几执行的，在时间窗口内创建了几次job，执行了几次job，当然可以加上window_start_time来查看具体执行的日期。
5、查看自动收集任务执行时间窗口
SQL> select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;
WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ ----------------------------------------------- ----- -------- MONDAY_WINDOW 26-NOV-12 10.00.00.000000 PM PRC TRUE ENABLED TUESDAY_WINDOW 27-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED WEDNESDAY_WINDOW 28-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED THURSDAY_WINDOW 29-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED FRIDAY_WINDOW 30-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED SATURDAY_WINDOW 01-DEC-12 06.00.00.000000 AM PRC FALSE ENABLED SUNDAY_WINDOW 02-DEC-12 06.00.00.000000 AM PRC FALSE ENABLED
我将系统日期改为11月26日22点以后，MONDAY_WINDOW执行时间窗口自动激活。
6、查询自动收集任务正在执行的JOB
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';
这个查询没有结果也很正常，只有job正在运行时，该查询才有结果。
7、与时间窗口相关的视图
查询自动收集任务所属时间窗口组
SQL> select client_name,window_group from dba_autotask_client where client_name='auto optimizer stats collection';CLIENT_NAME WINDOW_GROUP ---------------------------------------- --------------------------------------------------- auto optimizer stats collection ORA$AT_WGRP_OS
查询自动收集任务所属时间窗口组详细信息
SQL> select * from dba_scheduler_window_groups where window_group_name='ORA$AT_WGRP_OS';WINDOW_GROUP_NA ENABL NUMBER_OF_WINDOWS NEXT_START_DATE COMMENTS --------------- ----- ----------------- ----------------------------------- --------------------------------------------- ORA$AT_WGRP_OS TRUE 7 26-NOV-12 10.00.00.000000 PM PRC auto optimizer stats collection
查看自动收集任务所属时间窗口组包含的子时间窗口
SQL> select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';WINDOW_GROUP_NA WINDOW_NAME --------------- ------------------------------ ORA$AT_WGRP_OS MONDAY_WINDOW ORA$AT_WGRP_OS TUESDAY_WINDOW ORA$AT_WGRP_OS WEDNESDAY_WINDOW ORA$AT_WGRP_OS THURSDAY_WINDOW ORA$AT_WGRP_OS FRIDAY_WINDOW ORA$AT_WGRP_OS SATURDAY_WINDOW ORA$AT_WGRP_OS SUNDAY_WINDOW
查看子时间窗口信息
SQL> select a.window_name,a.next_start_date,a.active from dba_scheduler_windows a 2 inner join dba_scheduler_wingroup_members b on a.window_name = b.window_name 3 where b.window_group_name='ORA$AT_WGRP_OS';WINDOW_NAME NEXT_START_DATE ACTIV ------------------------------ ----------------------------------- ----- MONDAY_WINDOW 26-NOV-12 10.00.00.000000 PM PRC TRUE TUESDAY_WINDOW 27-NOV-12 10.00.00.000000 PM PRC FALSE WEDNESDAY_WINDOW 28-NOV-12 10.00.00.000000 PM PRC FALSE THURSDAY_WINDOW 29-NOV-12 10.00.00.000000 PM PRC FALSE FRIDAY_WINDOW 30-NOV-12 10.00.00.000000 PM PRC FALSE SATURDAY_WINDOW 01-DEC-12 06.00.00.000000 AM PRC FALSE SUNDAY_WINDOW 02-DEC-12 06.00.00.000000 AM PRC FALSE
通过这个查询可以看出DBA_AUTOTASK_WINDOW_CLIENTS视图的信息其实和上面的结果一样。
总结一下统计信息自动收集任务运行的步骤：
首先是dba_autotask_task-->dba_autotask_client建立自动执行任务
再根据时间窗口及资源组建立自动执行作业
dba_autotask_client-->dba_scheduler_window_groups-->dba_scheduler_windows
-->dba_scheduler_jobs
dba_autotask_client-->dba_scheduler_job_classes
相关视图：
dba_autotask_task
dba_autotask_client
dba_autotask_client_job
dba_autotask_window_clients
dba_autotask_client_history
dba_scheduler_jobs
dba_scheduler_job_classes
dba_scheduler_window_groups
dba_scheduler_windows
dba_scheduler_wingroup_members
自定义自动收集任务时间窗口：
How to use an own Maintenance Window for the Statistics Collection in 11g [ID 1300313.1]
MOS相关文档： FAQ: Automatic Statistics Collection [ID 1233203.1]
887
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
