在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_WINDOW和WEEKEND_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_DATE是12: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_WINDOW和WEEKEND_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;

Oracle 10g GATHER_STATS_JOB作業週六6點開始執行的原因

相關文章