Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Job Queues - Question on execution times and sample
A copy of this was sent to jal_at_esc188.expansion.com
(if that email address didn't require changing)
On 16 Sep 1998 17:29:25 GMT, you wrote:
>Hello,
>
>I am inseting records into Oracle's Job Queue and the next date and interval of
>execution in the documentation is not all that clear. Here are some samples. Could
>someone please clarify when and often these get executed. Most of my Oracle servers
>check the job queues betweeen 30 seconds and 1 minute.
>
>#1
>execute dbms_job.submit(:j, 'chk_test;',sysdate,'sysdate+5/24/60');
>
will get executed right away (as soon as the job queue kicks in anyway) and then every (5 days/24/60) which happens to be 5 minutes (5 would be 5 days, 5/24 would be 5 hours, 5/24/60 is 5 minutes...)
>#2
>execute dbms_job.submit(:j, 'start_test;',sysdate,'sysdate+1');
>
this happens every day, once a day...
>Thanks,
>
>JasonL
Both of these scheduling techniques above are subject to 'sliding'. That is, the next is computed when the job starts -- the job may not start on time due to some other job taking a long time or the database not being up. Since these always use relative times, the start time of the job will invariably slip over time. For example, using the sysdate, 'sysdate+1' example. Lets say I submit that at 1pm initially. It gets run at 1:01pm (thats the first time the queue got inspected for example). So now the NEXT will be tomorrow at 1:01 pm (not 1pm). Tomorrow, the job gets run at 1:02pm -- next gets set to 1:03 pm and so on. Over time the job 'slips'.
I prefer to use non-relative times. If you want a job to run at 2am every night the database is up, something like:
..., trunc(sysdate)+1+2/24, 'trunc(sysdate)+1+2/24'
The trunc(sysdate) will take todays date and strip off the time. Adding one to that gives us tomorrow. Adding 2/24 adds 2 hours to that time. We get 2am the next morning in both our start and next fields -- regardless of the 'slippage', the job is always scheduled for 2am.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 16 1998 - 13:22:11 CDT