Thanks for the question, Sergey.

Asked: October 28, 2003 - 8:39 am UTC

Last updated: May 24, 2004 - 4:04 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hello Tom!

During daily observation I have noticed, that DBA_JOBS.NEXT_DATE value
shifts a little every day. I've made test case, take a look please:

create or replace package TEST1 as
procedure wait1;
end;
/

create or replace package body TEST1 as
procedure wait1 is
begin
sys.dbms_lock.sleep(10);
end;
end;
/

select text from sys.dba_errors where owner=user;

create or replace package TEST2 as
procedure wait2;
end;
/

create or replace package body TEST2 as
procedure wait2 is
begin
sys.dbms_lock.sleep(100);
end;
end;
/

select text from sys.dba_errors where owner=user;

DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT(jobno, 'TEST1.WAIT1;', sysdate, 'sysdate + 1');
DBMS_JOB.RUN(jobno);
END;
/
commit;

DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT(jobno, 'TEST2.WAIT2;', sysdate, 'sysdate + 1');
DBMS_JOB.RUN(jobno);
END;
/
commit;

show parameter job
column "Sysdate" format a15
column last_date format a15
column next_date format a15
column "What" format a15

select job, to_char(sysdate, 'dd-mon hh24:mi:ss') "Sysdate",
to_char(last_date, 'dd-mon hh24:mi:ss') last_date,
to_char(next_date, 'dd-mon hh24:mi:ss') next_date,
substr(what, 1, 14) "What", total_time
from sys.dba_jobs;

And the output is:

Package created.

Package body created.

no rows selected

Package created.

Package body created.

no rows selected

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

Commit complete.

JOB Sysdate LAST_DATE NEXT_DATE What TOTAL_TIME
---------- --------------- --------------- --------------- --------------- ----------
2 06-oct 14:16:56 06-oct 14:08:31 07-oct 14:08:31 TEST1.WAIT1; 11
3 06-oct 14:16:56 06-oct 14:08:42 07-oct 14:08:42 TEST2.WAIT2; 102

Next day:

JOB Sysdate LAST_DATE NEXT_DATE What TOTAL_TIME
---------- --------------- --------------- --------------- --------------- ----------
2 07-oct 14:20:43 07-oct 14:08:36 08-oct 14:08:36 TEST1.WAIT1; 21
3 07-oct 14:20:43 07-oct 14:08:46 08-oct 14:08:46 TEST2.WAIT2; 205

Next day:

JOB Sysdate LAST_DATE NEXT_DATE What TOTAL_TIME
---------- --------------- --------------- --------------- --------------- ----------
2 08-oct 17:01:39 08-oct 14:08:41 09-oct 14:08:41 TEST1.WAIT1; 31
3 08-oct 17:01:39 08-oct 14:08:51 09-oct 14:08:51 TEST2.WAIT2; 308

You see, seconds are shifting, and it's not very depends of time for own job.
How can I use this mechanism for business critical operations?
For one of my servers time shift reaches more than 10 minutes now.
Is something wrong?
Is it related with [BUG:2649244] "JOBS may not run at scheduled intervals"?

Thanks In Advance.

and Tom said...

it is the expected, defined behavior.

the job runs "on or about the scheduled time". The queue is inspected every now and again and the job is run. So, I job initially scheduled to run at midnight -- might run at 12:01 am. Then, the next date is computed -- "sysdate+1". Now the job is scheduled to run tommorow at 12:01 am -- NOT midnight.

So, you need to use date functions that evaluate to a fixed time in the future.

For example, you want a job to run at 1am every night:

trunc(sysdate)+1+1/24

that will always evaluate to 1am TOMORROW -- regardless of when you evaluate it.

Rating

(10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dave, October 28, 2003 - 2:39 pm UTC

This is unrelated to the shifting behaviour ...

Because DBMS_JOB does not guarantee an exact start time, one available methodology for getting closer to an exact time would seem to be to schedule the job to start a little early then start the procedure with a call to dbms_lock.sleep()

For example, to run a job at three am exactly, you might schedule the procedure for 2:55 am, then call ...

dbms_lock.sleep((trunc(sysdate)+(3/24)-sysdate)*24*60*60);

October 28, 2003 - 9:00 pm UTC

that would just cause cascading problems as the job queue process would be STUCK sleeping.

Nothing but nothing is going to be dead on exact

Great!

Christo Kutrovsky, October 28, 2003 - 4:13 pm UTC

Great idea Dave !!! that will come handy one day!.

October 28, 2003 - 9:07 pm UTC

please don't

a) no assurance the job starts with enough time to "sleep" in the first place

b) you'll consume a job queue process for the duration of the sleep!

DB JOB time shift

Sergey, October 29, 2003 - 2:20 am UTC

Many thanks for the explanation!
The major idea is that NEXT DATE is computing AFTER end of job as I understand.

October 29, 2003 - 6:47 am UTC

start of job, not end.

if you have my book "Expert one on one Oracle" -- i've a pretty comprehensive section on DBMS_JOB and scheduling with -- how to set up static timeframes, how to do custom schedulings, how to use it correctly.

Ref: Sleeping

Dave, October 29, 2003 - 11:08 am UTC

"a) no assurance the job starts with enough time to "sleep" in the first place"

Start it earlier, surely?

"b) you'll consume a job queue process for the duration of the sleep! "

Is there an alternative method you can suggest for getting closer to a guaranteed start time, Tom?

October 29, 2003 - 11:49 am UTC

like a dog chasing its tail.

So, now you have a job queue process sitting there sleeping (and not running anything -- meaning other jobs that are trying to start earlier to sleep themselves cannot -- meaning everything starts getting backed up)

Normally, if the database is up and running, if there are sufficient job queue processes configured, the jobs will in fact start within SECONDS of their scheduled start.

So, it is not really "an issue".

if you didn't have sufficient job queues configured -- sleeping on them would certainly ensure jobs would be delayed even more! so, if you didn't have enough configured, that would only make it worse.

So, assuming you have them configured appropriately -- you would then just make sure the db was up. if the db wasn't up -- sort of moot.

So, in real life, jobs start within seconds of their scheduled start and its not an issue. but to prevent the inevitable "slide" in time - you need to use "anchored" next functions -- functions that evaluate to a fixed time in the future, not just sysdate+1. A 20 second slide over 10 days starts to add up

Dave, October 30, 2003 - 12:14 pm UTC

Ah, did you mean that the session might not start at the scheduled time because of insufficient job_queue_processes?

I interpreted your about a 12:00 job maybe starting at 12:01 to mean that the scheduler might start it later than expected even if there were sufficient queues, because it's not necessarily accurate in it's start time, or because (more likely guess) the job_queue_interval (in 8i) does not match the granularity of the time you want the job to start -- eg. it might wake up every ten minutes, but you want the job to start at 12:05.

October 30, 2003 - 9:41 pm UTC

it is a function of

o insufficient job queues
o jobs hogging job queues (variation of above)
o the fact that in 8i, 9i, for ever -- job queues are polled. they will NEVER be exact, never. they will slip by seconds at the very very least. even if you use dbms_lock.sleep -- you yourself would be subject to a slip of a second, which over time would add up!

Combine your ideas for a (maybe) useful technique

Michael, October 31, 2003 - 4:14 am UTC

I think, if you simple connect both of your ideas to a combined technique you get the best method to let a job start at an exact date (although I have to admit that I cannot think of a case where this could be mandatory)
So if you want to have an action starting nearly exactly at 13:00 each day (assuming a default JOB_QUEUE_INTERVAL of 60):

use trunc(sysdate+1)+13/24-2/1440 for next_date of your job
use dbms_lock.sleep((trunc(sysdate)+13/24-sysdate)*86400) at the beginning of your job

Dave, October 31, 2003 - 12:41 pm UTC

Well, as i originally said .. "This is unrelated to the shifting behaviour ...". I think you missed that comment.

The DBMS_LOCK.SLEEP() methodology was not meant to counteract the dbms_job start time shifting -- i take it as gospel that you would specify the start time with a function that prevents slippage, and I also assumed that there were sufficient job queue processes.

It was just in case dbms_job didn't start jobs with the precision required by the application _for_other_reasons_. If it was not acceptable that a job might start 30 seconds late, then starting it five minutes early and letting it sleep until the correct start time might give a more accurate start time for the main functionality of the procedure.

You know what might be a useful enhancement? Prioritisation within the job queue, or multiple queues with usage restricted through different permission sets.

November 01, 2003 - 12:11 pm UTC

operative words here are "might give " but that conflicts with "not acceptable" :)

10g will pretty address that last part

DB JOB time shift

Jürgen, November 03, 2003 - 3:56 am UTC

Hi, I had the same problem running a job that takes data once a hour. The job should run 10 minutes after a full hour and we know the problem of time shifting.
We solved this problem by using a function instead of sysdate which gets the next date for our interval.
So no matter what sysdate is we round to the next hour +10 minutes of course this is also possible for days, months and minutes ...

It was the first time I visited this site because I'm know reading Effective Oracle by Design and the page is great and the book also!

A reader, April 19, 2004 - 4:53 am UTC

Time Shifting

Rk, May 24, 2004 - 2:21 pm UTC

I am also trying to run a stored procedure after every 15 minutes using following code
dbms_job.submit(jobno,'datapull.start_process;',
sysdate,'sysdate + 1/98',null);

suppose my sysdate is 1:00 it should run at 1:15 but it is not running like that it is starting at 1:22 or taking more time to start.

May 24, 2004 - 4:04 pm UTC

the job queues are not 100% precise.

you control the frequency with which they poll via

job_queue_interval (deprecated in later releases, does it every couple of seconds all by itself, no need to touch it)

and the NUMBER of jobs via

job_queue_processes


if you for example, have a single job queue but run 15 jobs -- all scheduled at 1:15, they will run one after the other.


So, either

o your interval is too large
o your queue size is too small
o you really didn't submit and commit the job at 1pm.

Also, 1/98 is not 15 minutes, close but not quite:


 
  1* select sysdate, sysdate+1/98, sysdate+(1/24/60)*15 from dual
ops$tkyte@ORA9IR2> /
 
SYSDATE              SYSDATE+1/98         SYSDATE+(1/24/60)*15
-------------------- -------------------- --------------------
24-may-2004 15:49:47 24-may-2004 16:04:29 24-may-2004 16:04:47

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Great!

DB JOB time shift

Ref: Sleeping

Combine your ideas for a (maybe) useful technique

DB JOB time shift

Time Shifting

More to Explore

PL/SQL demos

PL/SQL docs