Oracle作業(yè)(JOB)更新next_date的探討
2024-08-29 13:45:01
供稿：網(wǎng)友
Oracle作業(yè)(JOB)更新next_date的探討
本文作者：kamus(kamus@itpub.net)
摘要:本文通過(guò)實(shí)驗(yàn)和事件跟蹤來(lái)分析Oracle Job執(zhí)行過(guò)程中修改下次執(zhí)行時(shí)間的機(jī)制。
有些人問(wèn)，Oracle的JOB在設(shè)定完next_date和interval之后，到底是什么時(shí)候決定下一次運(yùn)行時(shí)間的。可以歸納成以下幾個(gè)問(wèn)題。
1． 假設(shè)我們的JOB設(shè)定第一次運(yùn)行的時(shí)間是12:00，運(yùn)行的間隔是1小時(shí)，JOB運(yùn)行需要耗時(shí)30分鐘，那么第二次運(yùn)行是在13:00還是13:30？
2． 假如是在13:00那是不是說(shuō)明只要JOB一開始運(yùn)行，next_date就被重新計(jì)算了？
3． JOB的下一次運(yùn)行會(huì)受到上一次運(yùn)行時(shí)間的影響嗎？假如受到影響，如何可以避免這個(gè)影響而讓JOB在天天的指定時(shí)刻運(yùn)行？
本文通過(guò)一些實(shí)驗(yàn)和跟蹤來(lái)解釋上面的所有問(wèn)題。
首先我們選擇一個(gè)測(cè)試用戶，假設(shè)該用戶名為kamus。
由于我們?cè)趯?shí)驗(yàn)用的存儲(chǔ)過(guò)程中會(huì)用到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ù)庫(kù)，創(chuàng)建我們測(cè)試使用的存儲(chǔ)過(guò)程sp_test_next_date。
create or replace procedure sp_test_next_date as
p_jobno number;
P_nextdate date;
begin
--將調(diào)用此存儲(chǔ)過(guò)程的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)用該存儲(chǔ)過(guò)程的JOB，定義interval為天天一次，也就是這次執(zhí)行以后，下次執(zhí)行時(shí)間應(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 過(guò)程已成功完成。
jobno
---------
1
然后我們手工執(zhí)行存儲(chǔ)過(guò)程，執(zhí)行完畢以后再手工從user_jobs視圖中獲得JOB的下次執(zhí)行時(shí)間，可以看到在存儲(chǔ)過(guò)程中修改的JOB的下次執(zhí)行時(shí)間已經(jīng)生效，變成了當(dāng)前時(shí)間的30分鐘以后，而不是默認(rèn)的1天以后。
SQL> conn kamus
請(qǐng)輸入口令:
已連接。
SQL> set serverout on
SQL> exec sp_test_next_date();
JOB執(zhí)行中的next_date: 2004-12-02 00:44:11
PL/SQL 過(guò)程已成功完成。
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
我們?cè)偈止み\(yùn)行JOB，看看這次的結(jié)果，可以發(fā)現(xiàn)JOB沒有運(yùn)行完畢以前被修改了的下次運(yùn)行時(shí)間跟JOB運(yùn)行完畢以后再次手工檢索user_jobs視圖獲得的下次運(yùn)行時(shí)間已經(jīng)不相同了。由此我們可以得出一個(gè)結(jié)論，next_date是在JOB運(yùn)行完畢以后被Oracle自動(dòng)修改的，而不是在JOB剛開始運(yùn)行的時(shí)候，因?yàn)槲覀冊(cè)诖鎯?chǔ)過(guò)程中修改的next_date在JOB運(yùn)行結(jié)束之后又被修改為默認(rèn)的1天以后了。
SQL> exec dbms_job.run(1);
JOB執(zhí)行中的next_date: 2004-12-02 00:54:52
PL/SQL 過(guò)程已成功完成。
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)在我們?cè)俅涡薷拇鎯?chǔ)過(guò)程，輸出存儲(chǔ)過(guò)程開始執(zhí)行的時(shí)間，便于跟執(zhí)行完畢以后的JOB下次執(zhí)行時(shí)間進(jìn)行比較。
create or replace procedure sp_test_next_date as
p_jobno number;
P_nextdate date;
begin
--輸出JOB剛開始執(zhí)行的時(shí)間
dbms_output.put_line(' JOB開始執(zhí)行的時(shí)間: '
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
--將調(diào)用此存儲(chǔ)過(guò)程的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;
重新進(jìn)行測(cè)試，我們可以發(fā)現(xiàn)JOB的next_date是JOB開始執(zhí)行時(shí)間的1天以后，而不是JOB結(jié)束時(shí)間的1天以后（因?yàn)镴OB結(jié)束需要經(jīng)過(guò)10秒鐘）
SQL> exec dbms_job.run(1);
JOB開始執(zhí)行的時(shí)間: 2004-12-02 00:38:24
JOB執(zhí)行中的next_date: 2004-12-02 01:08:24
PL/SQL 過(guò)程已成功完成。
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)說(shuō)明了兩個(gè)問(wèn)題。就是：JOB在運(yùn)行結(jié)束之后才會(huì)更新next_date，但是計(jì)算的方法是JOB剛開始的時(shí)間加上interval設(shè)定的間隔。
下面我們通過(guò)trace來(lái)再次求證這個(gè)結(jié)論。
SQL> ALTER session SET EVENTS '10046 trace name context forever, level 12';
會(huì)話已更改。
SQL> exec dbms_job.run(1);
PL/SQL 過(guò)程已成功完成。
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
會(huì)話已更改。
執(zhí)行完畢以后在udump目錄中查看生成的trace文件。假如我們用tkprof來(lái)格式化這個(gè)trace文件然后再查看格式化后的結(jié)果，我們會(huì)感到很詫異。因?yàn)樵诟袷交戤叺腟QL執(zhí)行順序中，更新job$表的語(yǔ)句出現(xiàn)在dbms_job.next_date語(yǔ)句之前，也就是看上去是Oracle先按照interval自動(dòng)更新了JOB的next_date，然后才繼續(xù)往下執(zhí)行存儲(chǔ)過(guò)程中定義的next_date更新語(yǔ)句，而這樣顯然無(wú)法解釋我們?cè)谏厦娴膶?shí)驗(yàn)中看到的結(jié)果。
但是當(dāng)我們跳過(guò)tkprof而直接去查看生成的trace文件，就會(huì)恍然大悟，同時(shí)也印證了steve adams在ixora上提到的觀點(diǎn)：tkprof格式化完的結(jié)果會(huì)省略一些信息，甚至在有時(shí)候會(huì)給我們錯(cuò)誤的信息。
直接查看trace文件，我們可以看到如下的執(zhí)行順序：
1． parse cursor #10（oracle根據(jù)interval和先前保存的this_date字段值更新job$表的語(yǔ)句，包括更新failures, last_date, next_date, total等）
2． parse cursor #15（存儲(chǔ)過(guò)程中的begin dbms_job.next_date語(yǔ)句）
3． binds cursor #15（將加上了30分鐘的時(shí)間綁定到cursor #15上）
4． exec cursor #15（執(zhí)行cursor #15）
5． wait cursor #11（經(jīng)歷一個(gè)PL/SQL lock timer事件，也就是存儲(chǔ)過(guò)程中執(zhí)行的dbms_lock.sleep方法）
6． binds cursor #10（將JOB剛開始執(zhí)行時(shí)候的時(shí)間綁定到cursor #10上）
7． exec cursor #10（執(zhí)行cursor #10）
也就是說(shuō)雖然更新job$的語(yǔ)句被很早地解析過(guò)了，但是直到JOB運(yùn)行結(jié)束時(shí)這個(gè)被解析過(guò)的游標(biāo)才開始作變量綁定進(jìn)而開始執(zhí)行。
正是因?yàn)榻馕鰑pdate sys.job$語(yǔ)句的時(shí)間早于解析begin dbms_job.next_date語(yǔ)句的時(shí)間，所以tkprof的結(jié)果將前者放在了前面。
由于trace文件過(guò)長(zhǎng)，所以不在本文中貼出了，假如有愛好可以發(fā)郵件給我。我的郵件地址是：kamus@itpub.net
本文的最后一部分，解答本文開頭提出的第三個(gè)問(wèn)題，也就是：
JOB的下一次運(yùn)行會(huì)受到上一次運(yùn)行時(shí)間的影響嗎？假如受到影響，如何可以避免這個(gè)影響而讓JOB在天天的指定時(shí)刻運(yùn)行？
JOB的下一次運(yùn)行時(shí)間是會(huì)受上一次影響的，假如我們的interval僅僅是sysdate+1/24這樣的形式的話，無(wú)疑，上次執(zhí)行的時(shí)間再加上1小時(shí)就是下次執(zhí)行的時(shí)間。那么假如JOB因?yàn)槟承┰蜓舆t執(zhí)行了一次，這樣就會(huì)導(dǎo)致下一次的執(zhí)行時(shí)間也同樣順延了，這通常不是我們希望出現(xiàn)的現(xiàn)象。
解決方法很簡(jiǎn)單，只需要設(shè)定正確的interval就可以了。
比如，我們要JOB在天天的凌晨3:30執(zhí)行而不管上次執(zhí)行到底是幾點(diǎn)，只需要設(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)雖然通過(guò)select rowid from table返回的結(jié)果已經(jīng)是擴(kuò)展ROWID格式（Data Object number + File + Block + ROW）了，但是oracle內(nèi)部檢索數(shù)據(jù)仍然在使用限制ROWID格式（Block number.Row number.File number）。
本文涉及到的額外知識(shí)可以參看我的其它技術(shù)文章：
1． 通過(guò)事件跟蹤SQL執(zhí)行的后臺(tái)步驟
2． Oracle等待事件，比如本文提到的PL/SQL lock timer
3． ROWID格式
作者簡(jiǎn)介：
張樂奕，網(wǎng)名kamus
曾任ITPUB Oracle認(rèn)證版版主,現(xiàn)任itpub Oracle治理版版主.
現(xiàn)任職于北京某大型軟件公司，首席DBA，主要負(fù)責(zé)證券行業(yè)的全國(guó)十?dāng)?shù)處核心交易系統(tǒng)數(shù)據(jù)庫(kù)治理及維護(hù)工作。
熱切關(guān)注Oracle技術(shù)和其它相關(guān)技術(shù)，出沒于各大數(shù)據(jù)庫(kù)技術(shù)論壇，目前是中國(guó)最大的Oracle技術(shù)論壇www.itpub.net的數(shù)據(jù)庫(kù)治理版版主，
閱讀更多技術(shù)文章和隨筆可以登錄我的個(gè)人blog。
http://blog.cdsn.net/kamus。
