Oracle Job (JOB) Discussion on Next_Date
Author: kamus (kamus@itpub.net)
This article can be reproduced any, please be sure to indicate the original source and the author information and this statement by hyperlink when reprinted. Http://blog.9cbs.net/kamus/archive/2004/12/201377.aspx
Abstract: This paper modifies the next execution time during the Oracle Job execution by experiment and event tracking.
Some people ask, Oracle's Job after setting up next_date and interval, when is the next runtime. It can be summarized into the following issues.
1. Suppose our Job setting the first time of running is 12:00, the running interval is 1 hour, Job runs for 30 minutes, then the second run is 13:00 or 13:30?
2. If it is at 13:00, it means that as long as Job is running, NEXT_DATE is recalculated?
3. Will Job's next running is affected by the last runtime? If it is affected, how to avoid this impact and let Job run in a daily specified time?
4. Suppose our Job settings the first time is 12:00, running interval is 30 minutes, Job is running for 1 hour, then running at 12:30 or 13:00, it will eventually Report an error?
This article explains all the problems above through some experiments and tracking.
First we choose a test user, assume that the user is named Kamus.
Since we use the DBMS_LOCK package during the stored procedure of the experiment, you need to grant Kamus users to use the DBMS_LOCK package first by the SYS user.
D: / Temp> SQLPLUS "/ as sysdba"
SQL * Plus: Release
9.2.0
.5.0 - Production On Wednesday December 1 23:56:32 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connect to: Oracle9i
Enterprise
Edition Release
9.2.0
.5.0 - Production with the partitioning, OLAP AND ORACLE DATA MINING OPTION JSERVER RELEASE
9.2.0
.5.0 - Production
SQL> Grant Execute On DBMS_LOCK to KAMUS;
Authorized success.
Then use the Kamus user to log in to the database, create the stored procedure sp_test_next_date we test.
create or replace procedure sp_test_next_date as p_jobno number; P_nextdate date; begin - stored procedure calls this job is set to 30 minutes after next_date 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; '; ; '; DBMS_OUTPUT.PUT_LINE (NEXT_DATE:' || to_Char (p_nextdate, 'YYYY-MM-DD HH24: MI: SS')); - Wait for 10 seconds to exit execution dbms_lock.sleep (Seconds = > 10); End sp_test_next_date; create Job that calls the stored procedure, define Interval to once a day, that is, after this execution, the next execution time should be 1 day.
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 /
The PL / SQL process has been successfully completed.
Jobno --------- 1
Then we handle the stored procedure, and then manually get Job's next execution time from the USER_JOBS view, you can see the next execution time of the JOB modified during the stored procedure has taken effect, turn it into the current time 30 minutes. Later, not the default one day.
SQL> CONN KAMUS Please enter the password: connected. SQL> SET ServerOut on SQL> EXEC SP_TEST_NEXT_DATE (); Job execution NEXT_DATE: 2004-12-02 00:44:11
The PL / SQL process has been successfully completed.
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
We will manually run Job and look at this result, you can find that Job has not runs until the next runtime that has been modified, and then manually retrieves the next runtime after running again. The next runtime is not the same. Thus we can draw a conclusion, next_date is automatically modified by Oracle after running in Job, not when Job just starts running, because the next_date modified during the stored procedure is changed to the end of the job. The default 1 day later. SQL> EXEC DBMS_Job.Run (1); NEXT_DATE: 2004-12-02 00:54:52
The PL / SQL process has been successfully completed.
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
Now we modify the stored procedure again, the time to output the stored procedure, which is easy to compare the next execution time after the execution is completed.
Create or replace process_test_next_date; p_nextdate date; begin - Output Job just start executing time dbms_output.put_line ('Job started:' || to_char (sysdate, 'yyyy-mm-dd hh24: mi: SS ')); - job calls next_date set this storage procedure is 30 minutes later 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;'; - finished with editing check user_jobs view, the current output job next_date select next_date into P_nextdate from user_jobs where what = 'sp_test_next_date;'; dbms_output.put_line ( ' Next_date: '|| to_char (p_nextddate,' YYYY-MM-DD HH24: MI: SS ')); - Wait for 10 seconds to exit execution dbms_lock.sleep (seconds => 10); end sp_test_next_date;
Re-test, we can find that Job's next_date is 1 day after the job starts time execution time, not 1 day after the Job end time (because Job is required to pass 10 seconds)
SQL> EXEC DBMS_Job.Run (1); Job began to execute: 2004-12-02 00:38:24 JOB execution Next_Date: 2004-12-02 01:08:24
The PL / SQL process has been successfully completed.
SQL> SELECT TO_CHAR (Next_DATE, 'YYY-MM-DD HH24: MI: SS') Next_Date from user_jobs where what = 'sp_test_next_date;'; Next_date ------------------------------------------------------------------------------------------------------------------------ - 2004-12-03 00:38:24
At this point, we have explained two questions. YES: Job will only update next_date after running, but the calculated method is the time that Job just started with the interval set.
Below we will review this conclusion through TRACE.
SQL> ALTER Session Set Events '10046 Trace Name Context Forever, Level 12';
The session has changed.
SQL> EXEC DBMS_JOB.RUN (1);
The PL / SQL process has been successfully completed.
SQL> ALTER Session Set Events '10046 Trace Name Context Off';
The session has changed.
View the generated trace file in the UDump directory after execution. We will feel very surprised if we format this trace file with Tkprof and then view the results of formatting. Because in the formatted SQL execution order, the statement of the update Job $ table appears before the dbms_job.next_date statement, that is, oracle first automatically updates Job's next_date, then continue to perform the stored procedure The defined next_date update statement, which obviously does not explain the results we see in the experiment above.
But when we skip TkProf and go directly to see the generated trace file, it will suddenly realize that it also confirms that Steve Adams mentioned on Ixora: TKPROF formatted results will omit some information, or even sometimes give it Our wrong information.
Directly view the trace file, we can see the following execution order:
1. PARSE CURSOR # 10 (Oracle updates the statement of the Job $ table according to the INTERVAL and the previously saved this_date field value, including updates Failures, Last_Date, Next_Date, Total, etc.)
2. Parse Cursor # 15 (Begin DBMS_JOB.NEXT_DATE statement during storage procedure)
3. BINDS CURSOR # 15 (will be bind to Cursor # 15 with a 30 minute time)
4. EXEC CURSOR # 15 (executing Cursor # 15)
5. Wait Cursor # 11 (experience a PL / SQL Lock Timer event, that is, DBMS_LOCK.SLEEP method executed during storage procedures)
6. BINDS CURSOR # 10 (binding the job when Job just starts to CURSOR # 10)
7. EXEC CURSOR # 10 (Execute Cursor # 10)
That is to say, although the statement update Job $ is parsed very early, it is until the Job runs at the end of the job, which begins to make variable binding and start execution.
It is because parsing the time of the Update Sys.Job $ statement than parsing the time of the begin dbms_job.next_date statement, so the result of TKPROF put the former in front.
Next, we have entered another problem, and the fourth issue that is the first mentioned in this article:
Suppose our Job settings the first time is 12:00, running interval is 30 minutes, Job is running for 1 hour, then running at 12:30 or 13:00, it will eventually Report an error? By analyzing the trace file we can find updated next_date's SQL statement is:
Update sys.job $ set failies = 0, this_date = NULL, FLAG =: 1, last_date =: 2, next_date = greatest (: 3, sysdate), total = Total (Sysdate - NVL (this_date, sysdate) Where job = : 4
Note that the formula for updating the next_date field is Greatest (: 3, sysdate), here: 3 Binding is Job of this_date interval. So we guess it should actually have a comparison mechanism with the current time. If the time after executing Job is more late than the time calculated by this_date interval, then next_date is updated to the current time, that is, almost immediately Re-execute the Job.
In the same way, we also need to verify it through experiments.
Create a new stored procedure sp_test_next_date1, simply wait for 2 minutes, but we will call the JOB of this stored procedure to set it to 1 minute, see what the situation will be.
For more convenient comparison, we create a table to record the start time of each job execution.
SQL> CREATE TABLE T (CDATE DATE);
Table created
Create a script for the stored procedure
Create or Replace Procedure SP_TEST_NEXT_DATE11 ASBEGIN - Output Job Started Time INSERT INTO T (CDATE) (Sysdate); Commit; - Waiting for 120 seconds to exit DBMS_LOCK.SLEP (Seconds => 120); End sp_test_next_date1;
Create Job for calling this stored procedure
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 /
The PL / SQL process has been successfully completed.
Jobno --------- 7
Execute this job, then check the output in Table T over a period of time.
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
First we confirm that Job is successfully implemented, there is no error, then check the cdate field, and finding the time interval is about 2 minutes, that is, because the Job itself's interval setting is longer than the Job itself. So Oracle sets next_date to the time of each Job.
At the same time, we also noticed that every beginning, there are 4 seconds to 8 seconds of delay, and do not continue to be relentant, do not recognize this because the error calculated by Oracle itself, or the time is within, for example, starting Job Process. No matter what, we have already answered the fourth problem, even if the interval is shorter than the time of JOB execution, the entire job will continue, but the execution interval is time for Job.
Since the Trace file is too long, it is not posted in this article. If you are interested, you can send me an email. My email address is: kamus@itpub.net
The last part of this article, answers the third issue raised in this article, that is:
Will Job's next running is affected by the last runtime? If it is affected, how to avoid this impact and let Job run in a daily specified time?
Job's next runtime is subject to the previous impact. If our interval is just the form of Sysdate 1/24, it is undoubtedly, the time for the last execution is 1 hour is the next time. Then, if Job is executed once, this will result in the next execution time, which is usually not the phenomenon we want.
The solution is simple, just set the correct interval.
For example, we want Job to perform at 3:30 in the morning of the day, regardless of the last execution, just set up Interval to Trunc (Sysdate) 3.5 / 24 1. The complete SQL is as follows:
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: Discovered in the trace file Although the result returned by Select Rowid from Table is already extended RowID format (Data Object Number File Block Row), but Oracle internal retrieval data is still using restriction RowID format (Block Number.Row Number.File Number.
Additional knowledge involved in this article can see my other technical articles:
1. Background steps executed by event tracking SQL
2. Oracle Waiting for the event, such as the PL / SQL LOCK TIMER mentioned herein
3. RowID format
About the Author:
Zhang Lezhen, net name Kamus
Formerly ITPUB Oracle Certification Master, the current ITPUB Oracle Management Master.
He is currently working in a large software company in Beijing, chief DBA, mainly responsible for the management and maintenance of the core trading system of the country of the securities industry.
Eat paying attention to Oracle technology and other related technologies, out of the major database technology forums, currently China's largest Oracle technology forum www.itpub.net database management version of the master,
Read more technical articles and essays can log in to my personal blog. http://blog.cdsn.net/kamus.
