oracle 11g自动收集统计信息的时间是22:00--2:00这个时段往往是业务的高峰期,给本已紧张的系统带来更大的负担。所以,应该把自动执行的时间改到空闲的时段。
--获得当前自动收集统计信息的执行时间
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------------ ------------------------------------------------------------ ---------------------------------------------------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 0 4:0:0.0
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 0 20:0:0.0
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 0 20:0:0.0
--WINDOW_NAME:任务名
--REPEAT_INTERVAL:任务重复间隔时间
--DURATION:持续时间
修改的步骤:
--1.停止任务
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;
--2.修改任务的持续时间,单位是分钟
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
--3.开始执行时间,BYHOUR=2,表示2点开始执行
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
END;
--4.开启任务
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;
阅读(6538) | 评论(0) | 转发(1) |