有朋友会问“为什么我的在每天晚上10点钟运行的非常缓慢？”。有经验的朋友会想到为CBO提供了自动收集数据库对象统计信息的功能，称之为“”。与之对应的Job正是在周一至周五的晚上10:00到第二天早上的6:00以及周六周日全天这个中来完成的。
我们探索一下Automatic Statistics Collection的维护窗口在数据库中的定义。
1.统计信息自动采集对应的Job信息
数据库创建过程中创建的“GATHER_STATS_JOB”便是用于自动搜集统计数据的Job。该Job可以从dba_scheduler_jobs视图中获得。
sys@ora10g> col JOB_NAME for a16
sys@ora10g> col PROGRAM_NAME for a17
sys@ora10g> col SCHEDULE_NAME for a24
sys@ora10g> col JOB_CLASS for a20
sys@ora10g> select job_name, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
JOB_NAME PROGRAM_NAME SCHEDULE_NAME JOB_CLASS
---------------- ----------------- ------------------------ --------------------
GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS
sys@ora10g> select program_action from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG';
PROGRAM_ACTION
-------------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc
据以上信息可知，Job“GATHER_STATS_JOB”是使用program“GATHER_STATS_PROG”在维护窗口“MAINTENANCE_WINDOW_GROUP”中被调用。
“GATHER_STATS_PROG”使用存储过程“dbms_stats.gather_database_stats_job_proc”完成统计数据的收集。
2.确定执行窗口
目标出现了，我们仅需要知道“MAINTENANCE_WINDOW_GROUP”是如何定义的，便可以知道统计信息自动采集的调度时间。
sys@ora10g> select * from dba_scheduler_wingroup_members where window_group_name = 'MAINTENANCE_WINDOW_GROUP';
WINDOW_GROUP_NAME WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW
可见，使用的是“WEEKNIGHT_WINDOW”和“WEEKEND_WINDOW”这两个时间窗口。
进一步通过dba_scheduler_windows视图便可以得到具体维护窗口的定义信息。
sys@ora10g> select window_name, repeat_interval, duration from dba_scheduler_windows where window_name = 'WEEKNIGHT_WINDOW';
WINDOW_NAME
------------------------------
REPEAT_INTERVAL
-------------------------------------------------------------------
DURATION
-------------------------------------------------------------------
WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00
WEEKNIGHT_WINDOW时间窗口定义内容为：每周的一、二、三、四、五的22:00:00开始持续8小时。即，工作日每天晚上的22:00到第二天早上6:00，共8个小时可以用来完成统计数据自动采集的任务。
sys@ora10g> select window_name, repeat_interval, duration from dba_scheduler_windows where window_name = 'WEEKEND_WINDOW';
WINDOW_NAME
------------------------------
REPEAT_INTERVAL
-------------------------------------------------------------------
DURATION
-------------------------------------------------------------------
WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00
WEEKEND_WINDOW时间窗口定义内容为：每周的六、日的00:00:00开始持续2天。即，周六周日的全天都可以用来完成统计数据自动采集的任务。
以上便是统计信息自动采集Job执行窗口在数据库中的定义信息。
3.有关该维护窗口的创建脚本
对上述维护窗口的定义有兴趣的朋友可以参考一下catmwin.sql脚本。该脚本详细记录了维护窗口的定义过程。
sys@ora10g> !cat $ORACLE_HOME/rdbms/admin/catmwin.sql
Rem
Rem $Header: catmwin.sql 23-feb-2005.16:21:16 mtakahar Exp $
Rem
Rem catmwin.sql
Rem
Rem Copyright (c) 2003, , Oracle. All rights reserved.
Rem
Rem NAME
Rem catmwin.sql - Catalog script. for Maintenance WINdow
Rem
Rem DESCRIPTION
Rem Defines maintenance window and stats collection job.
Rem
Rem NOTES
Rem Must be run when connected as SYSDBA
Rem
Rem MODIFIED (MM/DD/YY)
Rem mtakahar 02/23/05 - #(4175406) change gather_stats_* comments
Rem mtakahar 09/15/04 - gather_stats_job termination callback
Rem ilistvin 07/14/04 - move set_attribute outside exception block
Rem smuthuli 04/26/04 - auto space advisor
Rem jxchen 12/19/03 - Set "restartable" attribute for GATHER_STATS_JOB
Rem schakkap 12/05/03 - stop auto stats collection at end of mgmt window
Rem evoss 12/02/03 -
Rem evoss 11/17/03 - add follow_default_timezone attr for and
Rem rramkiss 06/16/03 - flag system-managed objects
Rem rramkiss 06/16/03 - suppress already_exists errors
Rem jxchen 06/12/03 - Add job definition
Rem jxchen 06/04/03 - jxchen_mwin_main
Rem jxchen 05/12/03 - Created
Rem
-- Create weeknight window. Weeknight window is 10pm - 6am Mon - Fri.
BEGIN
BEGIN
dbms_scheduler.create_window(
window_name=>'WEEKNIGHT_WINDOW',
resource_plan=>NULL,
repeat_interval=>'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;' ||
'byminute=0; bysecond=0',
duration=>interval '480' minute,
comments=>'Weeknight window for maintenance task');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','SYSTEM',TRUE);
dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',
'FOLLOW_DEFAULT_TIMEZONE',TRUE);
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create weekend window. Weekend window is from 12am Saturday through 12am
-- Monday.
BEGIN
BEGIN
dbms_scheduler.create_window(
window_name=>'WEEKEND_WINDOW',
resource_plan=>NULL,
repeat_interval=>'freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0',
duration=>interval '2880' minute,
comments=>'Weekend window for maintenance task');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
dbms_scheduler.set_attribute('WEEKEND_WINDOW','SYSTEM',TRUE);
dbms_scheduler.set_attribute('WEEKEND_WINDOW',
'FOLLOW_DEFAULT_TIMEZONE',TRUE);
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create maintenance window group and add weeknight and weekend windows to it.
BEGIN
BEGIN
dbms_scheduler.create_window_group('MAINTENANCE_WINDOW_GROUP');
dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',
'WEEKNIGHT_WINDOW');
dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',
'WEEKEND_WINDOW');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
dbms_scheduler.set_attribute('MAINTENANCE_WINDOW_GROUP','SYSTEM',TRUE);
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create gather stats program.
BEGIN
dbms_scheduler.create_program(
program_name=>'gather_stats_prog',
program_type=>'STORED_PROCEDURE',
program_action=>'dbms_stats.gather_database_stats_job_proc',
number_of_arguments=>0,
enabled=>TRUE,
comments
=>'Oracle defined automatic optimizer statistics collection program');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create auto space advisor program.
BEGIN
dbms_scheduler.create_program(
program_name=>'auto_space_advisor_prog',
program_type=>'STORED_PROCEDURE',
program_action=>'dbms_space.auto_space_advisor_job_proc',
number_of_arguments=>0,
enabled=>TRUE,
comments=>'auto space advisor maintenance program');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create resource manager consumer group.
execute dbms_resource_manager.create_pending_area;
BEGIN
dbms_resource_manager.create_consumer_group(
consumer_group=>'AUTO_TASK_CONSUMER_GROUP',
comment=>'System maintenance task consumer group');
EXCEPTION
when others then
if sqlcode = -29357 then NULL;
else raise;
end if;
END;
/
execute dbms_resource_manager.submit_pending_area;
-- Create autotask job class
BEGIN
BEGIN
sys.dbms_scheduler.create_job_class(
job_class_name=>'AUTO_TASKS_JOB_CLASS',
resource_consumer_group=>'AUTO_TASK_CONSUMER_GROUP',
comments=>'System maintenance job class');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
dbms_scheduler.set_attribute('AUTO_TASKS_JOB_CLASS','SYSTEM',TRUE);
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create stats collection job
BEGIN
BEGIN
dbms_scheduler.create_job(
job_name=>'gather_stats_job',
program_name=>'gather_stats_prog',
job_class=>'auto_tasks_job_class',
schedule_name=>'MAINTENANCE_WINDOW_GROUP',
enabled=>TRUE,
auto_drop=>FALSE,
comments
=>'Oracle defined automatic optimizer statistics collection job');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
dbms_scheduler.set_attribute('gather_stats_job','stop_on_window_close',
true);
dbms_scheduler.set_attribute('gather_stats_job','restartable', true);
dbms_scheduler.set_attribute('gather_stats_job',
'user_operations_callback','dbms_stats.cleanup_stats_job_proc');
dbms_scheduler.set_attribute('gather_stats_job','user_callback_context',1);
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
-- Create auto space advisor maintenancejob
BEGIN
BEGIN
dbms_scheduler.create_job(
job_name=>'auto_space_advisor_job',
program_name=>'auto_space_advisor_prog',
job_class=>'auto_tasks_job_class',
schedule_name=>'MAINTENANCE_WINDOW_GROUP',
enabled=>TRUE,
auto_drop=>FALSE,
comments=>'auto space advisor maintenance job');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
dbms_scheduler.set_attribute('auto_space_advisor_job',
'stop_on_window_close', true);
dbms_scheduler.set_attribute('auto_space_advisor_job','restartable', true);
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/
4.小结
本文以“既要知其然，又要知其所以然”的原则阐述了有关10g中统计信息自动收集的执行窗口在数据库中的定义。这些时间窗口对于我们有什么样的指导作用呢？恭喜你，问到点子上了。最直接的指导意义在于：切记勿将需长时间才能完成的高压力任务安排在文中所述的时间窗口内；如业务需要必须在这些时间窗口中执行，我们需要酌情停止统计信息的自动采集功能！
Good luck.
11.01.02
-- The End --
本文转自glying 51CTO博客，原文链接：http://blog.51cto.com/liying/968607，如需转载请自行联系原作者
