Oracle作業(JOB)更新next

　　摘要:本文通過實驗和事件跟蹤來分析Oracle Job執行過程中修改下次執行時間的機制
　　
　　有些人問Oracle的JOB在設定完next_date和interval之後到底是什麼時候決定下一次運行時間的可以歸納成以下幾個問題
　　
　　． 假設我們的JOB設定第一次運行的時間是:運行的間隔是小時JOB運行需要耗時分鐘那麼第二次運行是在:還是:？
　　
　　． 如果是在:那是不是說明只要JOB一開始運行next_date就被重新計算了？
　　
　　． JOB的下一次運行會受到上一次運行時間的影響嗎？如果受到影響如何可以避免這個影響而讓JOB在每天的指定時刻運行？
　　
　　本文通過一些實驗和跟蹤來解釋上面的所有問題
　　
　　首先我們選擇一個測試用戶假設該用戶名為kamus
　　
　　由於我們在實驗用的存儲過程中會用到dbms_lock包所以需要由sys用戶先授予kamus用戶使用dbms_lock包的權限
　　
　　d:\Temp>sqlplus / as sysdba
　　
　　SQL*Plus: Release Production on 星期三 月 ::
　　
　　Copyright (c) Oracle Corporation All rights reserved
　　
　　連接到:
　　
　　Oraclei Enterprise Edition Release Production
　　
　　With the Partitioning OLAP and Oracle Data Mining options
　　
　　JServer Release 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設置為分鐘以後
　　select job into p_jobno from user_jobs where what = sp_test_next_date;;
　　execute immediate begin dbms_jobnext_date( || to_char(p_jobno) || sysdate+/);commit;end;;
　　修改完畢以後檢查user_jobs視圖輸出job目前的next_date
　　select next_date
　　into P_nextdate
　　from user_jobs
　　where what = sp_test_next_date;;
　　dbms_outputput_line(JOB執行中的next_date: ||
　　to_char(p_nextdateYYYYMMDD HH:MI:SS));
　　等待秒再退出執行
　　dbms_locksleep(seconds => );
　　end sp_test_next_date;
　　
　　創建調用該存儲過程的JOB定義interval為每天一次也就是這次執行以後下次執行時間應該在天以後
　　
　　SQL> variable jobno number;
　　
　　SQL> BEGIN
　　
　　 DBMS_JOBSUBMIT(job => :jobno
　　
　　 what => sp_test_next_date;
　　
　　 next_date => SYSDATE
　　
　　 interval => SYSDATE+);
　　
　　 COMMIT;
　　
　　 END;
　　
　　 /
　　
　　PL/SQL 過程已成功完成
　　
　　jobno
　　
　　
　　
　　
　　
　　然後我們手工執行存儲過程執行完畢以後再手工從user_jobs視圖中獲得JOB的下次執行時間可以看到在存儲過程中修改的JOB的下次執行時間已經生效變成了當前時間的分鐘以後而不是默認的天以後
　　
　　SQL> conn kamus
　　
　　請輸入口令:
　　
　　已連接
　　
　　SQL> set serverout on
　　
　　SQL> exec sp_test_next_date();
　　
　　JOB執行中的next_date: ::
　　
　　PL/SQL 過程已成功完成
　　
　　SQL> col next_date for a
　　
　　SQL> select to_char(next_dateYYYYMMDD HH:MI:SS) next_date from user_jobs
　　
　　where what = sp_test_next_date;;
　　
　　NEXT_DATE
　　
　　我們再手工運行JOB看看這次的結果可以發現JOB沒有運行完畢以前被修改了的下次運行時間跟JOB運行完畢以後再次手工檢索user_jobs視圖獲得的下次運行時間已經不相同了由此我們可以得出一個結論next_date是在JOB運行完畢以後被Oracle自動修改的而不是在JOB剛開始運行的時候因為我們在存儲過程中修改的next_date在JOB運行結束之後又被修改為默認的天以後了
　　
　　SQL> exec dbms_jobrun();
　　
　　JOB執行中的next_date: ::
　　
　　PL/SQL 過程已成功完成
　　
　　SQL> select to_char(next_dateYYYYMMDD HH:MI:SS) next_date from user_jobs
　　
　　where what = sp_test_next_date;;
　　
　　NEXT_DATE
　　
　　現在我們再次修改存儲過程輸出存儲過程開始執行的時間便於跟執行完畢以後的JOB下次執行時間進行比較
　　
　　create or replace procedure sp_test_next_date as
　　p_jobno number;
　　P_nextdate date;
　　begin
　　輸出JOB剛開始執行的時間
　　dbms_outputput_line( JOB開始執行的時間: ||
　　to_char(sysdate YYYYMMDD HH:MI:SS));
　　將調用此存儲過程的job的next_date設置為分鐘以後
　　select job into p_jobno from user_jobs where what = sp_test_next_date;;
　　execute immediate begin dbms_jobnext_date( || to_char(p_jobno) || sysdate+/);commit;end;;
　　修改完畢以後檢查user_jobs視圖輸出job目前的next_date
　　select next_date
　　into P_nextdate
　　from user_jobs
　　where what = sp_test_next_date;;
　　dbms_outputput_line( JOB執行中的next_date: ||
　　to_char(p_nextdateYYYYMMDD HH:MI:SS));
　　等待秒再退出執行
　　dbms_locksleep(seconds => );
　　end sp_test_next_date;
　　
　　重新進行測試我們可以發現JOB的next_date是JOB開始執行時間的天以後而不是JOB結束時間的天以後（因為JOB結束需要經過秒鐘）
　　
　　SQL> exec dbms_jobrun();
　　
　　JOB開始執行的時間: ::
　　
　　JOB執行中的next_date: ::
　　
　　PL/SQL 過程已成功完成
　　
　　SQL> select to_char(next_dateYYYYMMDD HH:MI:SS) next_date from user_jobs
　　
　　where what = sp_test_next_date;;
　　
　　NEXT_DATE
　　
　　至此我們已經說明了兩個問題就是JOB在運行結束之後才會更新next_date但是計算的方法是JOB剛開始的時間加上interval設定的間隔
　　
　　下面我們通過trace來再次求證這個結論
　　
　　SQL> ALTER SESSION SET EVENTS trace name context forever level ;
　　
　　會話已更改
　　
　　SQL> exec dbms_jobrun();
　　
　　PL/SQL 過程已成功完成
　　
　　SQL> ALTER SESSION SET EVENTS trace name context off;
　　
　　會話已更改
　　
　　執行完畢以後在udump目錄中查看生成的trace文件如果我們用tkprof來格式化這個trace文件然後再查看格式化後的結果我們會感到很詫異因為在格式化完畢的SQL執行順序中更新job$表的語句出現在dbms_jobnext_date語句之前也就是看上去是Oracle先按照interval自動更新了JOB的next_date然後才繼續往下執行存儲過程中定義的next_date更新語句而這樣顯然無法解釋我們在上面的實驗中看到的結果
　　
　　但是當我們跳過tkprof而直接去查看生成的trace文件就會恍然大悟同時也印證了steve adams在ixora上提到的觀點tkprof格式化完的結果會省略一些信息甚至在有時候會給我們錯誤的信息
　　
　　直接查看trace文件我們可以看到如下的執行順序
　　
　　． parse cursor #（oracle根據interval和先前保存的this_date字段值更新job$表的語句包括更新failures last_date next_date total等）
　　
　　． parse cursor #（存儲過程中的begin dbms_jobnext_date語句）
　　
　　． binds cursor #（將加上了分鐘的時間綁定到cursor #上）
　　
　　． exec cursor #（執行cursor #）
　　
　　． wait cursor #（經歷一個PL/SQL lock timer事件也就是存儲過程中執行的dbms_locksleep方法）
　　
　　． binds cursor #（將JOB剛開始執行時候的時間綁定到cursor #上）
　　
　　． exec cursor #（執行cursor #）
　　
　　也就是說雖然更新job$的語句被很早地解析過了但是直到JOB運行結束時這個被解析過的游標才開始作變量綁定進而開始執行
　　
　　正是因為解析update sysjob$語句的時間早於解析begin dbms_jobnext_date語句的時間所以tkprof的結果將前者放在了前面
　　
　　由於trace文件過長所以不在本文中貼出了如果有興趣可以發郵件給我我的郵件地址是
　　
　　本文的最後一部分解答本文開頭提出的第三個問題也就是
　　
　　JOB的下一次運行會受到上一次運行時間的影響嗎？如果受到影響如何可以避免這個影響而讓JOB在每天的指定時刻運行？
　　
　　JOB的下一次運行時間是會受上一次影響的如果我們的interval僅僅是sysdate+/這樣的形式的話無疑上次執行的時間再加上小時就
From:http://tw.wingwit.com/Article/program/Oracle/201311/18096.html