Oracle作業(yè)(JOB)更新next_date的探討
2024-08-29 13:29:32
供稿：網(wǎng)友
oracle作業(yè)(job)更新next_date的探討
本文作者：kamus([email protected])
摘要:本文通過實驗和事件跟蹤來分析oracle job執(zhí)行過程中修改下次執(zhí)行時間的機制。
有些人問，oracle的job在設(shè)定完next_date和interval之后，到底是什么時候決定下一次運行時間的?？梢詺w納成以下幾個問題。
1． 假設(shè)我們的job設(shè)定第一次運行的時間是12:00，運行的間隔是1小時，job運行需要耗時30分鐘，那么第二次運行是在13:00還是13:30？
2． 如果是在13:00那是不是說明只要job一開始運行，next_date就被重新計算了？
3． job的下一次運行會受到上一次運行時間的影響嗎？如果受到影響，如何可以避免這個影響而讓job在每天的指定時刻運行？
本文通過一些實驗和跟蹤來解釋上面的所有問題。
首先我們選擇一個測試用戶，假設(shè)該用戶名為kamus。
由于我們在實驗用的存儲過程中會用到dbms_lock包，所以需要由sys用戶先授予kamus用戶使用dbms_lock包的權(quán)限。
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;
授權(quán)成功。
然后用kamus用戶登錄數(shù)據(jù)庫，創(chuàng)建我們測試使用的存儲過程sp_test_next_date。
create or replace procedure sp_test_next_date as
p_jobno number;
p_nextdate date;
begin
--將調(diào)用此存儲過程的job的next_date設(shè)置為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執(zhí)行中的next_date: ' ||
to_char(p_nextdate,'yyyy-mm-dd hh24:mi:ss'));
--等待10秒再退出執(zhí)行
dbms_lock.sleep(seconds => 10);
end sp_test_next_date;
創(chuàng)建調(diào)用該存儲過程的job，定義interval為每天一次，也就是這次執(zhí)行以后，下次執(zhí)行時間應(yīng)該在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
然后我們手工執(zhí)行存儲過程，執(zhí)行完畢以后再手工從user_jobs視圖中獲得job的下次執(zhí)行時間，可以看到在存儲過程中修改的job的下次執(zhí)行時間已經(jīng)生效，變成了當(dāng)前時間的30分鐘以后，而不是默認(rèn)的1天以后。
sql> conn kamus
請輸入口令:
已連接。
sql> set serverout on
sql> exec sp_test_next_date();
job執(zhí)行中的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，看看這次的結(jié)果，可以發(fā)現(xiàn)job沒有運行完畢以前被修改了的下次運行時間跟job運行完畢以后再次手工檢索user_jobs視圖獲得的下次運行時間已經(jīng)不相同了。由此我們可以得出一個結(jié)論，next_date是在job運行完畢以后被oracle自動修改的，而不是在job剛開始運行的時候，因為我們在存儲過程中修改的next_date在job運行結(jié)束之后又被修改為默認(rèn)的1天以后了。
sql> exec dbms_job.run(1);
job執(zhí)行中的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
現(xiàn)在我們再次修改存儲過程，輸出存儲過程開始執(zhí)行的時間，便于跟執(zhí)行完畢以后的job下次執(zhí)行時間進行比較。
create or replace procedure sp_test_next_date as
p_jobno number;
p_nextdate date;
begin
--輸出job剛開始執(zhí)行的時間
dbms_output.put_line(' job開始執(zhí)行的時間: ' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
--將調(diào)用此存儲過程的job的next_date設(shè)置為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執(zhí)行中的next_date: ' ||
to_char(p_nextdate,'yyyy-mm-dd hh24:mi:ss'));
--等待10秒再退出執(zhí)行
dbms_lock.sleep(seconds => 10);
end sp_test_next_date;
重新進行測試，我們可以發(fā)現(xiàn)job的next_date是job開始執(zhí)行時間的1天以后，而不是job結(jié)束時間的1天以后（因為job結(jié)束需要經(jīng)過10秒鐘）
sql> exec dbms_job.run(1);
job開始執(zhí)行的時間: 2004-12-02 00:38:24
job執(zhí)行中的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
至此，我們已經(jīng)說明了兩個問題。就是：job在運行結(jié)束之后才會更新next_date，但是計算的方法是job剛開始的時間加上interval設(shè)定的間隔。
下面我們通過trace來再次求證這個結(jié)論。
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';
會話已更改。
執(zhí)行完畢以后在udump目錄中查看生成的trace文件。如果我們用tkprof來格式化這個trace文件然后再查看格式化后的結(jié)果，我們會感到很詫異。因為在格式化完畢的sql執(zhí)行順序中，更新job$表的語句出現(xiàn)在dbms_job.next_date語句之前，也就是看上去是oracle先按照interval自動更新了job的next_date，然后才繼續(xù)往下執(zhí)行存儲過程中定義的next_date更新語句，而這樣顯然無法解釋我們在上面的實驗中看到的結(jié)果。
但是當(dāng)我們跳過tkprof而直接去查看生成的trace文件，就會恍然大悟，同時也印證了steve adams在ixora上提到的觀點：tkprof格式化完的結(jié)果會省略一些信息，甚至在有時候會給我們錯誤的信息。
直接查看trace文件，我們可以看到如下的執(zhí)行順序：
1． parse cursor #10（oracle根據(jù)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（執(zhí)行cursor #15）
5． wait cursor #11（經(jīng)歷一個pl/sql lock timer事件，也就是存儲過程中執(zhí)行的dbms_lock.sleep方法）
6． binds cursor #10（將job剛開始執(zhí)行時候的時間綁定到cursor #10上）
7． exec cursor #10（執(zhí)行cursor #10）
也就是說雖然更新job$的語句被很早地解析過了，但是直到j(luò)ob運行結(jié)束時這個被解析過的游標(biāo)才開始作變量綁定進而開始執(zhí)行。
正是因為解析update sys.job$語句的時間早于解析begin dbms_job.next_date語句的時間，所以tkprof的結(jié)果將前者放在了前面。
由于trace文件過長，所以不在本文中貼出了，如果有興趣可以發(fā)郵件給我。我的郵件地址是：[email protected]
本文的最后一部分，解答本文開頭提出的第三個問題，也就是：
job的下一次運行會受到上一次運行時間的影響嗎？如果受到影響，如何可以避免這個影響而讓job在每天的指定時刻運行？
job的下一次運行時間是會受上一次影響的，如果我們的interval僅僅是sysdate+1/24這樣的形式的話，無疑，上次執(zhí)行的時間再加上1小時就是下次執(zhí)行的時間。那么如果job因為某些原因延遲執(zhí)行了一次，這樣就會導(dǎo)致下一次的執(zhí)行時間也同樣順延了，這通常不是我們希望出現(xiàn)的現(xiàn)象。
解決方法很簡單，只需要設(shè)定正確的interval就可以了。
比如，我們要job在每天的凌晨3:30執(zhí)行而不管上次執(zhí)行到底是幾點，只需要設(shè)置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文件中發(fā)現(xiàn)雖然通過select rowid from table返回的結(jié)果已經(jīng)是擴展rowid格式（data object number + file + block + row）了，但是oracle內(nèi)部檢索數(shù)據(jù)仍然在使用限制rowid格式（block number.row number.file number）。
本文涉及到的額外知識可以參看我的其它技術(shù)文章：
1． 通過事件跟蹤sql執(zhí)行的后臺步驟
2． oracle等待事件，比如本文提到的pl/sql lock timer
3． rowid格式
作者簡介：
張樂奕，網(wǎng)名kamus
曾任itpub oracle認(rèn)證版版主,現(xiàn)任itpub oracle管理版版主.
現(xiàn)任職于北京某大型軟件公司，首席dba，主要負(fù)責(zé)證券行業(yè)的全國十?dāng)?shù)處核心交易系統(tǒng)數(shù)據(jù)庫管理及維護工作。
熱切關(guān)注oracle技術(shù)和其它相關(guān)技術(shù)，出沒于各大數(shù)據(jù)庫技術(shù)論壇，目前是中國最大的oracle技術(shù)論壇www.itpub.net的數(shù)據(jù)庫管理版版主，
閱讀更多技術(shù)文章和隨筆可以登錄我的個人blog。
http://blog.cdsn.net/kamus。
注冊會員，創(chuàng)建你的web開發(fā)資料庫，
