修改Oracle10g、11g系统自动统计Job的运行时间属性
Oracle 10g和11g,都有自动收集数据库统计信息的任务(10G和11G的JOB名不同),但是任务的执行时间可能与我们期望的不同,比如:周末两个全天都要运行,会影响系统性能,下面的方式可修改:
一.修改10g自动统计的时间属性
--GATHER_STATS_JOB
--查看SCHEDULER_JOBS
select job_name from dba_scheduler_running_jobs;
SELECT job_name , SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS;
select * from dba_scheduler_wingroup_members where
window_group_name='MAINTENANCE_WINDOW_GROUP';
/* WINDOW_GROUP_NAME WINDOW_NAME
1 MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
2 MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW
*/
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='MAINTENANCE_WINDOW_GROUP';
/*
WINDOW_NAME REPEAT_INTERVAL DURATION
1 WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI ;byhour=22;byminute=0; bysecond=0 +000 08:00:00
2 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute =0;bysecond=0 +002 00:00:00
*/
--下面修改配置(使用sys用户登录oracle执行)
sqlplus sys/xxx@xxxdb as sysdba
set linesize 120
--修改WEEKEND_WINDOW的配置 (改成和WEEKNIGHT_WINDOW相同,即周一~周五,每日的22:00向后8小时,至次日凌晨6点)
begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq =daily;byday=SAT,SUN;byhour=22;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+000
08:00:00');