Oracle Job (Job) update next

Oracle
Discussion on Oracle Job (Job) Update next_date



The author of this article: Kamus (Kamus@itpub.net)



Summary: This article analyzes the mechanism for modifying the next execution time during Oracle job execution through experiments and event tracking.



Some people ask when Oracle's job will determine the next run time after Next_date and interval are set. Can be summed up into the following problems.

1. Suppose our job set the first time to run 12:00, the interval is 1 hours, the job will take 30 minutes to run, then the second run is 13:00 or 13:30?

2. If it's at 13:00, is that an indication that the next_date was recalculated as soon as the job started running?

3. Will the next run of the job be affected by the last run time? If affected, how can you avoid this effect and let the job run at a specified time of day?



In this paper, some experiments and tracking are used to explain all the above problems.



First we select a test user, assuming that the user name is Kamus.

Since we use the Dbms_lock package in the lab stored procedure, it is necessary for the SYS user to first grant Kamus users permission to use the Dbms_lock package.



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 options

Jserver Release 9.2.0.5.0-production



Sql> Grant execute on Dbms_lock to Kamus;



The authorization was successful.



Then use the Kamus user to log in to the database to create the stored procedure sp_test_next_date that we tested.



Create or replace procedure sp_test_next_date as
P_jobno number;
P_nextdate date;
Begin
--Set the next_date of the job that calls this stored procedure to 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; ';
--Check user_jobs view after modification, output job current next_date
Select Next_date
Into P_nextdate
From User_jobs
where what = ' sp_test_next_date; ';
Dbms_output.put_line (' Next_date in Job execution: ' | |
To_char (p_nextdate, ' Yyyy-mm-dd HH24:MI:SS '));
--wait 10 seconds before quitting execution
Dbms_lock.sleep (seconds => 10);
End Sp_test_next_date;



Create a job that invokes the stored procedure, defining interval to be once a day, after this execution, the next execution time should be 1 days later.



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 completed successfully.



Jobno

---------

1



Then we execute the stored procedure manually, and then manually get the next execution time for the job from the User_jobs view, and you can see that the next execution time for the job modified in the stored procedure has taken effect, becoming 30 minutes after the current time, not the default 1 days later.



Sql> Conn Kamus

Please enter password:

is connected.

Sql> set Serverout on

Sql> exec sp_test_next_date ();

next_date:2004-12-02 00:44:11 in Job execution



The PL/SQL process has completed successfully.



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 run the job manually, and look at this result, we can find that the next run time that the job has been modified before it has been run is not the same as the next time it has been manually retrieved after the job has been run, and then user_jobs the view again. From this we can conclude that Next_date was automatically modified by Oracle after the job was run, not when the job was first run, because the next_date that we modified in the stored procedure were modified to the default 1 days after the job was run.



sql> exec Dbms_job.run (1);

next_date:2004-12-02 00:54:52 in Job execution



The PL/SQL process has completed successfully.



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 that the stored procedure starts to execute, and it is easy to compare with the next execution time of the job after completion.



Create or replace procedure sp_test_next_date as
P_jobno number;
P_nextdate date;
Begin
--The time the output job was first executed
Dbms_output.put_line (' The time the job begins to execute: ' | |
To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS '));
--Set the next_date of the job that calls this stored procedure to 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; ';
--Check user_jobs view after modification, output job current next_date
Select Next_date
Into P_nextdate
From User_jobs
where what = ' sp_test_next_date; ';
Dbms_output.put_line (' Next_date in Job execution: ' | |
To_char (p_nextdate, ' Yyyy-mm-dd HH24:MI:SS '));
--wait 10 seconds before quitting execution
Dbms_lock.sleep (seconds => 10);
End Sp_test_next_date;



Re-test, we can find that the job's next_date is 1 days after the start of the job, not 1 days after the end of the job (because the job end takes 10 seconds)



sql> exec Dbms_job.run (1);

Job start time: 2004-12-02 00:38:24

next_date:2004-12-02 01:08:24 in Job execution



The PL/SQL process has completed successfully.



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



So far, we've explained two questions. That is, the job does not update next_date after the run is finished, but the method is calculated by the time the job starts, plus the interval set interval.



Let's go through trace again to verify this conclusion.

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 completed successfully.



Sql> ALTER session SET EVENTS ' 10046 trace name context off ';



The session has changed.



After execution, view the generated trace file in the Udump directory. We would be surprised if we used TKPROF to format this trace file and then look at the formatted results. Because in the formatted SQL execution order, the statement that updates the job$ table appears before the Dbms_job.next_date statement, that is, it appears that Oracle has automatically updated the job's next_date by interval. It then continues to execute the NEXT_DATE UPDATE statement defined in the stored procedure, which clearly does not explain the results we saw in the previous experiment.

But when we skip the tkprof and look directly at the generated trace file, we get a sense of what Steve Adams said in Ixora: Tkprof formatting ends up omitting some information, even giving us the wrong information sometimes.

To view the trace file directly, we can see the following order of execution:

1. Parse cursor #10 (Oracle updates statements for job$ tables based on interval and previously saved this_date field values, including update failures, Last_date, next_date, total, etc.)

2. Parse cursor #15 (BEGIN dbms_job.next_date statement in stored procedure)

3. Binds cursor #15 (plus 30 minutes of time to Cursor #15上)

4. EXEC cursor #15 (perform cursor #15)

5. Wait cursor #11 (through a PL/SQL lock timer event, the Dbms_lock.sleep method executed in the stored procedure)

6. Binds cursor #10 (bind the time at which the job was first performed to Cursor #10上)

7. EXEC cursor #10 (perform cursor #10)



That is, although the updated job$ statement was parsed very early, it was not until the end of the job that the parsed cursor started as a variable binding and then started executing.

Because the time to parse the update sys.job$ statement is earlier than the time to parse the BEGIN Dbms_job.next_date statement, the tkprof result puts the former in front.

Because the trace file is too long, it is not posted in this article, if you are interested can email me. My email address is: kamus@itpub.net



The last part of this article answers the third question raised at the beginning of this article, which is:

Will the next run of the job be affected by the last run time? If affected, how can you avoid this effect and let the job run at a specified time of day?

The next run of the job will be affected by the last time, and if our interval is just a form of sysdate+1/24, it is no doubt that the last execution time plus 1 hours is the next execution time. So if the job is delayed for some reason, it can cause the next execution time to be postponed, which is usually not what we want to happen.

The solution is simple, just need to set the correct interval on it.

For example, we want the job to execute at 3:30 every day, regardless of the last time it was executed, just set interval as 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: The trace file found that although the result returned through the Select rowID from table is already in the extended ROWID format (data Object number + File + block + ROW), Oracle internal retrieval data is still in use Restricts rowID format (block number. Row number. File number).



The additional knowledge covered in this article can be read in my other technical articles:

1. Background steps for SQL execution through event tracking

2. Oracle waits for events, such as the Pl/sql lock timer mentioned in this article

3. ROWID format



Author Introduction:

Zhang, net name Kamus

Itpub Oracle Certified version of the moderator, the current Itpub Oracle Management edition Moderator.

Currently serving in a large software company in Beijing, the chief DBA, is mainly responsible for the securities industry's national core trading system database management and maintenance work.

Keen attention to Oracle technology and other related technologies, haunt the major database technology forums, is currently China's largest Oracle technology forum www.itpub.net Database Management Edition Moderator,

Read more technical articles and essays that can be logged into my personal blog.
Http://blog.cdsn.net/kamus.