修改Oracle10g、11g系统自动统计Job的运行时间属性.doc

修改Oracle10g、11g系统自动统计Job的运行时间属性 Oracle 10g和11g,都有自动收集数据库统计信息的任务(10G和11G的JOB名不同),但是任务的执行时间可能与我们期望的不同,比如:周末两个全天都要运行,会影响系统性能,下面的方式可修改: 一.修改10g自动统计的时间属性--GATHER_STATS_JOB --查看SCHEDULER_JOBSselect 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_NAME1??????? MAINTENANCE_WINDOW_GROUP??????? WEEKNIGHT_WINDOW2??????? 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??????? DURATION1??????? WEEKNIGHT_WINDOW??????? freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0??????? +000 08:00:002??????? WEEKEND_WINDOW??????? freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0??????? +002 00:00:00*/ --下面修改配置(使用sys用户登录oracle执行)sqlplus sys/xxx@xxxdb as sysdbaset 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); 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=MAINTENANCE_WINDOW_GROUP; ?/*?????????? WINDOW_NAME??????? REPEAT_INTERVAL??????? DURATION1??????? WEEKNIGHT_WINDOW??????? freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0??????? +000 08:00:002??????? WEEKEND_WINDOW??????? freq=daily;byday=SAT,SUN;byhour=22;byminute=0;bysecond=0??????? +000 08:00:00*/ --若要还原成以前默认设置,可执行如下--周末两天都是全天:begin dbms_scheduler.set_attribute(WE