【Job】收集统计信息的job GATHER_STATS_JOB在上午十点执行

今天在ITPUB上有人发帖说，自动收集统计信息的job gather_stats_job 使用默认的设置，即晚上10点-上午6点期间收集统计信息。但是查询dba_scheduler_jobs视图的last_start_date字段却显示该Job在上午10点执行，因收集统计信息同样需要消耗资源，现在又是业务高峰期，影响了性能。究其原因，是因为时区没有设置+08:00所致。下面描述具体内容：

1. 查询job的运行时间点

SQL>Select JOB_NAME,ACTUAL_START_DATE  From DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='GATHER_STATS_JOB' order by 2 descJOB_NAME ACTUAL_START_DATE
------------------------------ ---------------------------------------------------------------------------
GATHER_STATS_JOB               13-3月 -14 10.00.01.679286 上午 +08:00
GATHER_STATS_JOB               12-3月 -14 10.00.02.456798 上午 +08:00
GATHER_STATS_JOB               11-3月 -14 10.00.02.781824 上午 +08:00
GATHER_STATS_JOB               08-3月 -14 07.00.02.830648 下午 +08:00
GATHER_STATS_JOB               08-3月 -14 11.00.01.974981 上午 +08:00
GATHER_STATS_JOB               07-3月 -14 11.00.03.438888 上午 +08:00
GATHER_STATS_JOB               06-3月 -14 11.00.04.535456 上午 +08:00
GATHER_STATS_JOB               05-3月 -14 11.00.03.836729 上午 +08:00
GATHER_STATS_JOB               04-3月 -14 11.00.01.532332 上午 +08:00
GATHER_STATS_JOB               01-3月 -14 07.00.04.710198 下午 +08:00
GATHER_STATS_JOB               01-3月 -14 11.00.02.282859 上午 +08:00
GATHER_STATS_JOB               28-2月 -14 11.00.03.339471 上午 +08:00
GATHER_STATS_JOB               27-2月 -14 11.00.03.214076 上午 +08:00
GATHER_STATS_JOB               26-2月 -14 11.00.03.338937 上午 +08:00
GATHER_STATS_JOB               25-2月 -14 11.00.01.924616 上午 +08:00
GATHER_STATS_JOB               22-2月 -14 07.00.03.967977 下午 +08:00
GATHER_STATS_JOB               22-2月 -14 11.00.02.670745 上午 +08:00
GATHER_STATS_JOB               21-2月 -14 11.00.03.191213 上午 +08:00
GATHER_STATS_JOB               20-2月 -14 11.00.02.411076 上午 +08:00
GATHER_STATS_JOB               19-2月 -14 11.00.02.654828 上午 +08:00
GATHER_STATS_JOB               18-2月 -14 11.00.03.348023 上午 +08:00
GATHER_STATS_JOB               15-2月 -14 07.00.04.578580 下午 +08:00
GATHER_STATS_JOB               15-2月 -14 11.00.03.246914 上午 +08:00
GATHER_STATS_JOB               14-2月 -14 11.00.02.622735 上午 +08:00
GATHER_STATS_JOB               13-2月 -14 11.00.03.346898 上午 +08:00
GATHER_STATS_JOB               12-2月 -14 11.00.01.055803 上午 +08:00
GATHER_STATS_JOB               11-2月 -14 11.00.02.597484 上午 +08:00
27 rows selected.

从上可以看到运行时间均为上午11点，还有3/8号的7点。

2. 查看gather_stats_jobs的对应的window

SQL> select job_name, job_type, program_name, schedule_name, job_class
  2   from dba_scheduler_jobs
  3   where job_name = 'GATHER_STATS_JOB';
JOB_NAME                       JOB_TYPE         PROGRAM_NAME                   SCHEDULE_NAME                  
------------------------------ ---------------- ------------------------------ ------------------------------ 
GATHER_STATS_JOB        GATHER_STATS_PROG              MAINTENANCE_WINDOW_GROUP    

SQL> select * 
  2  from DBA_SCHEDULER_WINGROUP_MEMBERS
  3  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';

WINDOW_GROUP_NAME                     WINDOW_NAME
-------------------------------------        ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

SQL> l
  1  select window_name, repeat_interval, duration
  2   from dba_scheduler_windows
  3* where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW')
SQL> /
WINDOW_NAME             REPEAT_INTERVAL                                                                                                    DURATION
--------------------------  ------------------------------------------------------------------------ -                     -------------------------
WEEKNIGHT_WINDOW  freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0   +000 08:00:00
WEEKEND_WINDOW      freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                                     +002 00:00:00

可以看到window的间隔是：周一至周五晚上十点开始，间隔8个小时，周六全天。所以不应该出现早上10点开始执行Job的情况。

3. 原因分析

3.1 查看DB所在的时区

SQL> select dbtimezone from dual;
DBTIME
---------
+00:00

DB时区为+00:00区，windows设置的窗口时间是按照+00:00时区的时间来与运行。数据库的时区是+00:00，而客户端的时区是+08:00，也就是说查询显示的结果要比实际的时间提前了8个小时。10:00 AM +08：00对应的时间是：04:00 AM +00:00区。

gather job对应的window是10:00 PM-6：00 AM 但是这是+00:00时区的时间，对应+08:00的时间就是6:00 AM --02:00 PM 。

周六是全天都可以执行，所以运行时间7:00PM可以不在6:00 AM --02:00 PM 时段。DB的时区是scheduler或window默认使用的时区。

3.2 查看scheduelr所使用的时区（时区是scheluer或window的一个属性）

set verify off
variable v_value VARCHAR2(1000);
BEGIN
  DBMS_SCHEDULER.get_scheduler_attribute (
    attribute => 'default_timezone',
    value     => :v_value);
END;
/
print v_value；
默认使用的时区是DB的时区，但可以修改。

4. 解决方法

4.1 修改window对应的使用的时区

begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('default_timezone','Asia/Shanghai');
end;
/

查看scheduler使用的时区：

set verify off
variable v_value VARCHAR2(1000);
BEGIN
  DBMS_SCHEDULER.get_scheduler_attribute (
    attribute => 'default_timezone',
    value     => :v_value);
END;
/
print v_value；

4.2 修改DB的时区

考虑DB的时区不正确，也最好修改掉DB的时区

 alter database set time_zone='+08:00';

但如果数据库中存在WITH LOCAL TIME ZONE类型的字段，则修改时区时报错无法修改。需要找出类型的字段删除后在修改，下面是找出该类型字段的脚本：

select u.name || '.' || o.name || '.' || c.name TSLTZcolumn 
  from sys.obj$ o, sys.col$ c, sys.user$ u 
where c.type# = 231
   and o.obj# = c.obj# 
   and u.user# = o.owner#;

这种方法相当于修改了整个DB的时区。

ballontt
2014/03/14

---The End---

微博：weibo.com/ballontt

如需转载，请标明出处和链接，谢谢！