Oracle作業(JOB)更新next_date的探討（轉載）

摘要:本文通過實驗和事件跟蹤來分析Oracle Job執行過程中修改下次執行時間的機制。

 

有些人問，Oracle的JOB在設定完next_date和interval之後，到底是什麼時候決定下一次執行時間的。可以歸納成以下幾個問題。

1． 假設我們的JOB設定第一次執行的時間是12:00，執行的間隔是1小時，JOB執行需要耗時30分鐘，那麼第二次執行是在13:00還是13:30？
－－JOB在執行結束之後才會更新next_date，但是計算的方法是JOB剛開始的時間加上interval設定的間隔。
則第二次執行是在13:00。

2． 如果是在13:00那是不是說明只要JOB一開始執行，next_date就被重新計算了？
－－OB在執行結束之後才會更新next_date。

3． JOB的下一次執行會受到上一次執行時間的影響嗎？如果受到影響，如何可以避免這個影響而讓JOB在每天的指定時刻執行？
－－JOB的下一次執行時間是會受上一次影響的，如果我們的interval僅僅是sysdate+1/24這樣的形式的話，無疑，上次執行的時間再加上1小時就是下次執行的時間。那麼如果JOB因為某些原因延遲執行了一次，這樣就會導致下一次的執行時間也同樣順延了，這通常不是我們希望出現的現象。

解決方法很簡單，只需要設定正確的interval就可以了。

比如，我們要JOB在每天的凌晨3:30執行而不管上次執行到底是幾點，只需要設定interval為trunc(SYSDATE)+3.5/24+1即可。

4． 假設我們的JOB設定第一次執行的時間是12:00，執行的間隔是30分鐘，JOB執行需要耗時1小時，那麼第二次執行是在12:30還是13:00還是根本就會報錯？
－－更新next_date欄位的公式是greatest(:3, sysdate)，此處的:3繫結的是job的this_date+interval。所以我們猜測實際上應該是有一個跟當前時間的比較機制，如果在執行完JOB之後的時間比按照this_date+interval計算出的時間更晚一些，那麼next_date就更新為當前時間，也就是幾乎會立刻再重新執行JOB。
也就是說，next_date,如果JOB 開始時間+間隔時間 小於 當前時間，那麼則取 當前時間，否則取 開始時間+間隔時間。

 

本文通過一些實驗和跟蹤來解釋上面的所有問題。

 

首先我們選擇一個測試使用者，假設該使用者名稱為kamus。

由於我們在實驗用的儲存過程中會用到dbms_lock包，所以需要由sys使用者先授予kamus使用者使用dbms_lock包的許可權。

 

d:/Temp>sqlplus "/ as sysdba"

 

SQL*Plus: Release 9.2.0.5.0 - Production on 星期三 12月 1 23:56:32 2004

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

連線到:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> grant execute on dbms_lock to kamus;

 

授權成功。

 

然後用kamus使用者登入資料庫，建立我們測試使用的儲存過程sp_test_next_date。

 

create or replace procedure sp_test_next_date as
  p_jobno    number;
  P_nextdate date;
begin
  --將呼叫此儲存過程的job的next_date設定為30分鐘以後
  select job into p_jobno from user_jobs where what ='sp_test_next_date;';
  execute immediate 'begin dbms_job.next_date(' || to_char(p_jobno) || ',sysdate+1/48);commit;end;';
  --修改完畢以後檢查user_jobs檢視，輸出job目前的next_date
  select next_date
    into P_nextdate
    from user_jobs
   where what = 'sp_test_next_date;';
  dbms_output.put_line('JOB執行中的next_date: ' ||
                       to_char(p_nextdate,'YYYY-MM-DD HH24:MI:SS'));
  --等待10秒再退出執行
  dbms_lock.sleep(seconds => 10);
end sp_test_next_date;

 

建立呼叫該儲存過程的JOB，定義interval為每天一次，也就是這次執行以後，下次執行時間應該在1天以後。

 

SQL> variable jobno number;

SQL> BEGIN

  2  DBMS_JOB.SUBMIT(job => :jobno,

  3  what => 'sp_test_next_date;',

  4  next_date => SYSDATE,

  5  interval => 'SYSDATE+1');

  6  COMMIT;

  7  END;

  8  /

 

PL/SQL 過程已成功完成。

 

jobno

---------

1

 

然後我們手工執行儲存過程，執行完畢以後再手工從user_jobs檢視中獲得JOB的下次執行時間，可以看到在儲存過程中修改的JOB的下次執行時間已經生效，變成了當前時間的30分鐘以後，而不是預設的1天以後。

 

SQL> conn kamus

請輸入口令:

已連線。

SQL> set serverout on

SQL> exec sp_test_next_date();

JOB執行中的next_date: 2004-12-02 00:44:11

 

PL/SQL 過程已成功完成。

 

SQL> col next_date for a20

SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs

where what = 'sp_test_next_date;';

 

NEXT_DATE

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

2004-12-02 00:44:11

 

我們再手工執行JOB，看看這次的結果，可以發現JOB沒有執行完畢以前被修改了的下次執行時間跟JOB執行完畢以後再次手工檢索user_jobs檢視獲得的下次執行時間已經不相同了。由此我們可以得出一個結論，next_date是在JOB執行完畢以後被Oracle自動修改的，而不是在JOB剛開始執行的時候，因為我們在儲存過程中修改的next_date在JOB執行結束之後又被修改為預設的1天以後了。

 

SQL> exec dbms_job.run(1);

JOB執行中的next_date: 2004-12-02 00:54:52

 

PL/SQL 過程已成功完成。

 

SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs

where what = 'sp_test_next_date;';

 

NEXT_DATE

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

2004-12-03 00:24:52

 

現在我們再次修改儲存過程，輸出儲存過程開始執行的時間，便於跟執行完畢以後的JOB下次執行時間進行比較。

 

create or replace procedure sp_test_next_date as
  p_jobno    number;
  P_nextdate date;
begin
  --輸出JOB剛開始執行的時間
  dbms_output.put_line(' JOB開始執行的時間: ' ||
                       to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
  --將呼叫此儲存過程的job的next_date設定為30分鐘以後
  select job into p_jobno from user_jobs where what ='sp_test_next_date;';
  execute immediate 'begin dbms_job.next_date(' || to_char(p_jobno) || ',sysdate+1/48);commit;end;';
  --修改完畢以後檢查user_jobs檢視，輸出job目前的next_date
  select next_date
    into P_nextdate
    from user_jobs
   where what = 'sp_test_next_date;';
  dbms_output.put_line(' JOB執行中的next_date: ' ||
                       to_char(p_nextdate,'YYYY-MM-DD HH24:MI:SS'));
  --等待10秒再退出執行
  dbms_lock.sleep(seconds => 10);
end sp_test_next_date;

 

重新進行測試，我們可以發現JOB的next_date是JOB開始執行時間的1天以後，而不是JOB結束時間的1天以後（因為JOB結束需要經過10秒鐘）

 

SQL> exec dbms_job.run(1);

JOB開始執行的時間: 2004-12-02 00:38:24

JOB執行中的next_date: 2004-12-02 01:08:24

 

PL/SQL 過程已成功完成。

 

SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs

where what = 'sp_test_next_date;';

 

NEXT_DATE

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

2004-12-03 00:38:24

 

至此，我們已經說明了兩個問題。就是：JOB在執行結束之後才會更新next_date，但是計算的方法是JOB剛開始的時間加上interval設定的間隔。

 

下面我們通過trace來再次求證這個結論。

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

 

會話已更改。

 

SQL> exec dbms_job.run(1);

 

PL/SQL 過程已成功完成。

 

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

 

會話已更改。

 

執行完畢以後在udump目錄中檢視生成的trace檔案。如果我們用tkprof來格式化這個trace檔案然後再檢視格式化後的結果，我們會感到很詫異。因為在格式化完畢的SQL執行順序中，更新job$表的語句出現在dbms_job.next_date語句之前，也就是看上去是Oracle先按照interval自動更新了JOB的next_date，然後才繼續往下執行儲存過程中定義的next_date更新語句，而這樣顯然無法解釋我們在上面的實驗中看到的結果。

但是當我們跳過tkprof而直接去檢視生成的trace檔案，就會恍然大悟，同時也印證了steve adams在ixora上提到的觀點：tkprof格式化完的結果會省略一些資訊，甚至在有時候會給我們錯誤的資訊。

直接檢視trace檔案，我們可以看到如下的執行順序：

1． parse cursor #10（oracle根據interval和先前儲存的this_date欄位值更新job$表的語句，包括更新failures, last_date, next_date, total等）

2． parse cursor #15（儲存過程中的begin dbms_job.next_date語句）

3． binds cursor #15（將加上了30分鐘的時間繫結到cursor #15上）

4． exec cursor #15（執行cursor #15）

5． wait cursor #11（經歷一個PL/SQL lock timer事件，也就是儲存過程中執行的dbms_lock.sleep方法）

6． binds cursor #10（將JOB剛開始執行時候的時間繫結到cursor #10上）

7． exec cursor #10（執行cursor #10）

 

也就是說雖然更新job$的語句被很早地解析過了，但是直到JOB執行結束時這個被解析過的遊標才開始作變數繫結進而開始執行。

正是因為解析update sys.job$語句的時間早於解析begin dbms_job.next_date語句的時間，所以tkprof的結果將前者放在了前面。

 

接下來我們進入另外一個問題的探討，本文最開始提到的第四個問題：

假設我們的JOB設定第一次執行的時間是12:00，執行的間隔是30分鐘，JOB執行需要耗時1小時，那麼第二次執行是在12:30還是13:00還是根本就會報錯？

 

通過分析trace檔案我們可以找到更新next_date的SQL語句是：

update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_date=greatest(:3,sysdate),total=total+(sysdate - nvl(this_date, sysdate)) where job=:4

 

注意到更新next_date欄位的公式是greatest(:3, sysdate)，此處的:3繫結的是job的this_date+interval。所以我們猜測實際上應該是有一個跟當前時間的比較機制，如果在執行完JOB之後的時間比按照this_date+interval計算出的時間更晚一些，那麼next_date就更新為當前時間，也就是幾乎會立刻再重新執行JOB。

 

同樣這樣的猜測我們也需要通過實驗來驗證一下。

建立一個新的儲存過程sp_test_next_date1，簡單地等待2分鐘，但是我們將呼叫這個儲存過程的JOB的interval設定為1分鐘，看看會有什麼情況。

為了更方便得比較，我們建立一個表用來記錄每次JOB執行的開始時間。

SQL> create table t (cdate date);

 

Table created

 

建立儲存過程的指令碼

 

create or replace procedure sp_test_next_date1 as
begin
  --輸出JOB開始執行的時間
  insert into t(cdate) values(sysdate);
  commit;
  --等待120秒退出  
  dbms_lock.sleep(seconds => 120);  
end sp_test_next_date1;

 

建立呼叫此儲存過程的JOB

 

SQL> variable jobno number;

SQL> BEGIN

  2  DBMS_JOB.SUBMIT(job => :jobno,

  3  what => 'sp_test_next_date1;',

  4  next_date => SYSDATE,

  5  interval => 'SYSDATE+1/1440');

  6  COMMIT;

  7  END;

  8  /

 

PL/SQL 過程已成功完成。

 

jobno

---------

7

 

執行此JOB，然後過一段時間開始檢查表t中的輸出。

 

SQL> select * from t order by cdate;

 

CDATE

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

2004-12-3 14:10:43

2004-12-3 14:12:47

2004-12-3 14:14:55

2004-12-3 14:16:59

2004-12-3 14:19:07

2004-12-3 14:21:11

 

6 rows selected

 

首先我們確認JOB每次都是成功執行了，並沒有任何報錯，然後檢查cdate欄位，發現時間間隔都是2分鐘左右，也就是說因為JOB本身的interval設定比JOB本身的執行時間要長，所以Oracle將next_date設定為每次JOB結束的時間。

同時我們也注意到，每次開始的時間都有4秒到8秒的延遲，沒有繼續深究，不確認這是因為oracle本身計算的誤差，還是內部比如啟動Job Process需要的時長。

不論如何，到此我們也已經回答了第四個問題，即使interval的時長短於JOB執行的時間，整個作業仍然會繼續進行，只是執行間隔變為了JOB真實執行的時長。

 

由於trace檔案過長，所以不在本文中貼出了，如果有興趣可以發郵件給我。我的郵件地址是：kamus@itpub.net

 

本文的最後一部分，解答本文開頭提出的第三個問題，也就是：

JOB的下一次執行會受到上一次執行時間的影響嗎？如果受到影響，如何可以避免這個影響而讓JOB在每天的指定時刻執行？

JOB的下一次執行時間是會受上一次影響的，如果我們的interval僅僅是sysdate+1/24這樣的形式的話，無疑，上次執行的時間再加上1小時就是下次執行的時間。那麼如果JOB因為某些原因延遲執行了一次，這樣就會導致下一次的執行時間也同樣順延了，這通常不是我們希望出現的現象。

解決方法很簡單，只需要設定正確的interval就可以了。

比如，我們要JOB在每天的凌晨3:30執行而不管上次執行到底是幾點，只需要設定interval為trunc(SYSDATE)+3.5/24+1即可。完整的SQL如下：

SQL> variable jobno number;

SQL> BEGIN

  2  DBMS_JOB.SUBMIT(job => :jobno,

  3  what => 'sp_test_next_date;',

  4  next_date => SYSDATE,

  5  interval => 'trunc(SYSDATE)+3.5/24+1');

  6  COMMIT;

  7  END;

  8  /

 

BTW：在trace檔案中發現雖然通過select rowid from table返回的結果已經是擴充套件ROWID格式（Data Object number + File + Block + ROW）了，但是oracle內部檢索資料仍然在使用限制ROWID格式（Block number.Row number.File number）。

 

本文涉及到的額外知識可以參看我的其它技術文章：

1． 通過事件跟蹤SQL執行的後臺步驟

2． Oracle等待事件，比如本文提到的PL/SQL lock timer

3． ROWID格式

 

 

作者簡介：

張樂奕，網名kamus

曾任ITPUB Oracle認證版版主,現任itpub Oracle管理版版主.  

現任職於北京某大型軟體公司，首席DBA，主要負責證券行業的全國十數處核心交易系統資料庫管理及維護工作。

熱切關注Oracle技術和其它相關技術，出沒於各大資料庫技術論壇，目前是中國最大的Oracle技術論壇www.itpub.net的資料庫管理版版主，

閱讀更多技術文章和隨筆可以登入我的個人blog。
http://blog.cdsn.net/kamus。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11990065/viewspace-708745/，如需轉載，請註明出處，否則將追究法律責任。