修改Oracle10g、11g系統自動統計Job的執行時間屬性

修改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 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');
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        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,SUN;byhour=22;byminute=0;bysecond=0        +000 08:00:00
*/

--若要還原成以前預設設定,可執行如下
--週末兩天都是全天:
begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+002 00:00:00');
end;
/

二.修改11g自動統計時間屬性
--檢視SCHEDULER_JOBS
select job_name from dba_scheduler_running_jobs;
SELECT * FROM DBA_SCHEDULER_JOBS;

select * from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP';
/*WINDOW_GROUP_NAME  WINDOW_NAME
MAINTENANCE_WINDOW_GROUP  MONDAY_WINDOW
MAINTENANCE_WINDOW_GROUP        TUESDAY_WINDOW
MAINTENANCE_WINDOW_GROUP        WEDNESDAY_WINDOW
MAINTENANCE_WINDOW_GROUP        THURSDAY_WINDOW
MAINTENANCE_WINDOW_GROUP        FRIDAY_WINDOW
MAINTENANCE_WINDOW_GROUP        SATURDAY_WINDOW
MAINTENANCE_WINDOW_GROUP        SUNDAY_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 in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
/*
     WINDOW_NAME  REPEAT_INTERVAL        DURATION
1        MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00
2        TUESDAY_WINDOW        freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
3        WEDNESDAY_WINDOW        freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
4        THURSDAY_WINDOW        freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
5        FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
6        SATURDAY_WINDOW        freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0        +000 20:00:00
7        SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0        +000 20:00:00
*/

--下面修改配置(使用sys使用者登入oracle執行)
sqlplus as sysdba

--修改SATURDAY_WINDOW、SUNDAY_WINDOW的配置 (改成和平常相同,即每日都是22:00向後4小時,至次日凌晨2點)
--下面的方式也可修改週六、週日的時間Window
begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=SAT;ByHour=22;ByMinute=0;BySecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=SUN;ByHour=22;ByMinute=0;BySecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 04: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        DURATION
1        MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00
2        TUESDAY_WINDOW        freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
3        WEDNESDAY_WINDOW        freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
4        THURSDAY_WINDOW        freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
5        FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
6        SATURDAY_WINDOW        Freq=daily;ByDay=SAT;ByHour=22;ByMinute=0;BySecond=0        +000 04:00:00
7        SUNDAY_WINDOW        Freq=daily;ByDay=SUN;ByHour=22;ByMinute=0;BySecond=0        +000 04:00:00
*/

三.相關檢視
1.SCHEDULER_JOBS
select job_name from dba_scheduler_running_jobs;
SELECT * FROM DBA_SCHEDULER_JOBS;

2.有關自動資料庫維護任務的資訊的檢視：
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_CLIENT_HISTORY; 

 

http://blog.csdn.net/waferleo/archive/2009/12/26/5073482.aspx