oracle 11g默认会自动收集统计信息,会有性能的消耗,我们可以根据实际情况关闭或者调整任务执行时间、执行时长
-- 停止周一的任务
BEGINDBMS_SCHEDULER.DISABLE(
name => '"SYS"."FRIDAY_WINDOW"',
force => TRUE);
END;
/
-- 修改任务持续时间,单位是分钟
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."FRIDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(240,'minute'));
END;
/
-- 修改任务开始时间 1点
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."FRIDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
END;
/
-- 启动任务,
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."FRIDAY_WINDOW"');
END;
/
-- 查看修改后的情况
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');
-- 查看历史执行情况
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history
WHERE client_name like '%stats%';
SELECT * FROM dba_autotask_client_history
WHERE client_name like '%stats%'
order by window_start_time;