对于automatic statistics collection，有些地方需要我们注意一下：
1、STATISTICS_LEVEL为TYPICAL或者ALL的时候会开启automatic statistics collection，如果要禁掉它，你可以通过将STATISTICS_LEVEL修改为BASIC。但oracle通常并不推荐这样做！如果你是因为automatic statistics collection跟你自己的统计信息的脚本在执行时间上有冲突，你可以通过修改WEEKNIGHT_WINDOW和WEEKEND_WINDOW的执行时间与方式来规避这种冲突。
2、从脚本catmwin.sql中可以非常清晰的看出其整体搭建流程。
catmwin.sql中有关automatic statistics collection的内容如下：
-- 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 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;
/
从这个脚本里我们可以看到automatic statistics collection的搭建流程实在是再清晰不过了！
377152.1中提到：
With Oracle Database
10 pm to 6 am every weekday
All weekend (Friday 10 pm to Monday 6 am)
就是说automatic statistics collection在周末的运行时间是从礼拜五晚上10点到礼拜一早上6点。
从这个脚本里我们也可以看到上述说法是不正确的！WEEKEND_WINDOW的真正执行时间是从礼拜五晚上12点到礼拜一早上0点。
3、要改WEEKEND_WINDOW，要先disable，然后改，再enable：
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
force=>TRUE);
END;
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(600, 'minute'));
END;
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."WEEKNIGHT_WINDOW"');
END;
Leave a comment