ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10g GATHER_STATS_JOB作业周六6点开始运行的原因

Oracle 10g GATHER_STATS_JOB作业周六6点开始运行的原因

原创 Linux操作系统 作者:is.x 时间:2012-02-27 21:08:17 1 删除 编辑

Oracle 10g下,GATHER_STATS_JOB作业默认会定时自动运行,来收集数据库对象的统计信息,这些统计信息收集后,会做为Oracle CBO优化器模式的一个重要判断依据。同时,当对象的行数被修改超过10%时,该对象才会被认为是陈旧的(stale),Oracle会考虑重新收集该对象信息。

 

在统计GATHER_STATS_JOB作业运行时间时,会有一个奇怪的现象,按照默认情况下,GATHER_STATS_JOB作业运行时间范围应该是周一至周五每天22:00至次日6:00,周六周日运行全天(即:周六0:00开始运行直至次日24:00)。不过实际情况是,每周六早上都是从6:00开始运行的。

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Solaris: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

 

SQL> select log_date,job_name,status,actual_start_date,run_duration from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' order by log_id;

 

LOG_DATE                               JOB_NAME             STATUS     ACTUAL_START_DATE                        RUN_DURATION

-------------------------------------- -------------------- ---------- ---------------------------------------- --------------------

25-JAN-12 10.00.57.340579 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  25-JAN-12 10.00.06.603204 PM +08:00      +000 00:00:51

26-JAN-12 10.00.41.857101 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  26-JAN-12 10.00.06.713738 PM +08:00      +000 00:00:35

27-JAN-12 10.00.54.152761 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  27-JAN-12 10.00.08.603152 PM +08:00      +000 00:00:46

28-JAN-12 06.00.47.750677 AM +08:00    GATHER_STATS_JOB     SUCCEEDED  28-JAN-12 06.00.07.672922 AM +08:00      +000 00:00:40

30-JAN-12 10.01.03.283879 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  30-JAN-12 10.00.08.723355 PM +08:00      +000 00:00:54

31-JAN-12 10.00.51.528858 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  31-JAN-12 10.00.06.714094 PM +08:00      +000 00:00:45

01-FEB-12 10.00.50.298293 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  01-FEB-12 10.00.08.724264 PM +08:00      +000 00:00:42

02-FEB-12 10.00.44.419627 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  02-FEB-12 10.00.05.363075 PM +08:00      +000 00:00:39

03-FEB-12 10.00.56.304368 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  03-FEB-12 10.00.06.803979 PM +08:00      +000 00:00:49

04-FEB-12 06.00.46.401232 AM +08:00    GATHER_STATS_JOB     SUCCEEDED  04-FEB-12 06.00.06.363523 AM +08:00      +000 00:00:40

06-FEB-12 10.00.58.751950 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  06-FEB-12 10.00.06.724115 PM +08:00      +000 00:00:52

07-FEB-12 10.00.53.086005 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  07-FEB-12 10.00.08.122822 PM +08:00      +000 00:00:45

08-FEB-12 10.00.58.393431 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  08-FEB-12 10.00.08.604050 PM +08:00      +000 00:00:50

09-FEB-12 10.00.51.080252 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  09-FEB-12 10.00.11.283296 PM +08:00      +000 00:00:40

10-FEB-12 10.01.02.159598 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  10-FEB-12 10.00.06.802890 PM +08:00      +000 00:00:55

11-FEB-12 06.00.51.472096 AM +08:00    GATHER_STATS_JOB     SUCCEEDED  11-FEB-12 06.00.10.883334 AM +08:00      +000 00:00:41

13-FEB-12 10.00.52.583083 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  13-FEB-12 10.00.07.523327 PM +08:00      +000 00:00:45

14-FEB-12 10.00.47.894097 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  14-FEB-12 10.00.05.882718 PM +08:00      +000 00:00:42

15-FEB-12 10.00.53.058736 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  15-FEB-12 10.00.11.272609 PM +08:00      +000 00:00:42

16-FEB-12 10.00.46.904091 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  16-FEB-12 10.00.08.733705 PM +08:00      +000 00:00:38

17-FEB-12 10.00.52.122642 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  17-FEB-12 10.00.05.412825 PM +08:00      +000 00:00:47

18-FEB-12 06.00.43.889252 AM +08:00    GATHER_STATS_JOB     SUCCEEDED  18-FEB-12 06.00.05.932977 AM +08:00      +000 00:00:38

20-FEB-12 10.00.54.141530 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  20-FEB-12 10.00.05.773721 PM +08:00      +000 00:00:48

21-FEB-12 10.00.45.277218 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  21-FEB-12 10.00.06.135190 PM +08:00      +000 00:00:39

22-FEB-12 10.00.55.291677 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  22-FEB-12 10.00.07.473469 PM +08:00      +000 00:00:48

23-FEB-12 10.00.44.962444 PM +08:00    GATHER_STATS_JOB     SUCCEEDED  23-FEB-12 10.00.08.022678 PM +08:00      +000 00:00:37

 

26 rows selected.

 

GATHER_STATS_JOB作业在被Oracle Scheduler Job调度时,使用了一个维护窗口组MAINTENANCE_WINDOW_GROUP,其由两个窗口组成:WEEKNIGHT_WINDOWWEEKEND_WINDOW

 

WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.

WEEKEND_WINDOW covers whole days Saturday and Sunday.

 

从下面的查询结果也可以看到,WEEKEND_WINDOW窗口NEXT_START_DATE12:00AM(这个就是0:00),但LAST_START_DATE却是6:00AM

 

SQL> select window_name,repeat_interval,next_start_date,last_start_date from dba_scheduler_windows;

 

WINDOW_NAME

------------------------------

REPEAT_INTERVAL

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------

NEXT_START_DATE                                                             LAST_START_DATE

--------------------------------------------------------------------------- ---------------------------------------------------------------------------

WEEKNIGHT_WINDOW

freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

24-FEB-12 10.00.00.100000 PM PRC                                            23-FEB-12 10.00.00.101507 PM PRC

 

WEEKEND_WINDOW

freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

25-FEB-12 12.00.00.000000 AM PRC                                            18-FEB-12 06.00.00.921655 AM PRC

 

究其原因,主要是由于WEEKNIGHT_WINDOWWEEKEND_WINDOW窗口的时间存在重叠(overlap)。

 

以下这段说明摘自Oracle官网:

 

Although Oracle does not recommend it, windows can overlap. Because only one window can be active at one time, the following rules are used to determine which window will be active when windows overlap:

l         If windows of the same priority overlap, the window that is active will stay open. However, if the overlap is with a window of higher priority, the lower priority window will close and the window with the higher priority will open. Jobs currently running that had a schedule naming the low priority window may be stopped depending on the behavior. you assigned when you created the job.

l         If at the end of a window there are multiple windows defined, the window with the highest priority will open. If all windows have the same priority, the window that has the highest percentage of time remaining will open.

l         An open window that is dropped will be automatically closed. At that point, the previous rule applies.

Whenever two windows overlap, an entry is written in the Scheduler log.

 

可以通过以下查询查看这两个窗口的优先级:

 

SQL> SELECT window_name,window_priority FROM dba_scheduler_windows;

 

WINDOW_NAME                    WINDOW_PRIORITY

------------------------------ ------------------------------

WEEKNIGHT_WINDOW               LOW

WEEKEND_WINDOW                 LOW

 

这两个窗口的优先级是一样的,都是LOW。所以当窗口时间出现重叠时,由于在重叠时间内,只能有一个窗口被打开,所以WEEKEND_WINDOW实际是周六早上6:00被打开的,所以GATHER_STATS_JOB作业周六也从早上6:00开始运行。

 

虽然这种设定比较能接受,不过Oracle自己都说不推荐windows互相overlap,自己定义的窗口却是overlap的。

 

其实对于实际的生产数据库,这样的设置并不是最优的,比如银行那种7*24小时的OLTP系统,相较平时,双休日一般都是交易的高峰期。在这样的时间段全天跑自动收集统计信息作业,其实并不合理。建议如果是生产系统数据库,上线前需要考虑的一件事就是:如何合理安排自动作业的运行时间。

 

以下给出调整GATHER_STATS_JOB作业时间的一种方法,假设定制在每天早上1:00运行:

 

SQL>BEGIN

DBMS_SCHEDULER.create_schedule (

schedule_name => ' GATHER_SCHEDULE ',

start_date => TRUNC(SYSDATE)+1/24,

repeat_interval => 'freq=daily;byday=MON, TUE, WED, THU, FRI,SAT,SUN;byhour=1;byminute=0;bysecond=0',

end_date => NULL,

comments => 'Repeats daily for ever');

END;

/

SQL> exec sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );

SQL> exec sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_STATS_JOB"', attribute =>'schedule_name', value => ' GATHER_SCHEDULE ');

SQL> exec sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' );

 

确认调整后的job时间

 

SQL> SELECT schedule_name,start_date FROM user_scheduler_schedules;

SQL> SELECT job_name, enabled FROM user_scheduler_jobs;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20750200/viewspace-717255/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    254189