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