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

Dear 丶 2022-08-20 11:18 310阅读 0赞

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  
\*/

\--下面修改配置(使用sys用户登录oracle执行)  
sqlplus [sys/xxx@xxxdb][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');  
end;  
/

\--若要还原成以前默认设置,可执行如下  
\--周末两天都是全天:  
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 [sys/xxx@xxxdb][sys_xxx_xxxdb] 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;

[sys_xxx_xxxdb]: mailto:sys/xxx@xxxdb