JOB queue

[nalanda]

Oracle 8i on Win 2K.

I am running a procedure every hour through DBMS_JOB.
Assume the job runs first time at
10:00:00 and it takes on an average 30seconds to finish the job, now the next time the job is run at
11:00:30 next time at
12:01:00
13:01:30
14:02:00 .............

At this rate after 24 hours the job is effectively running 12minutes(say) after the hour.
I want the job to run on the clock or atleast with in 1 minute after the hour.

Please can any body give any suggestions as to how I can achieve this.

Thanks in advance.

[jmodic]

You currently have interval defined as so0mething like 'SYSDATE + 1/24'. Doing it this way the time difference between the time when job should be run and the time when job was actually run will accumulate, resulting in time-drift of the actual job execution time.

If you want to avoid this, you have to "anchor" your time interval. If you wan't to execute job each full hour, you should set your job interval as:

'TRUNC(SYSDATE, 'HH') + 1/24'

This way, job might execute a few seconds after full hour (depending on JOB_QUEUE_INTERVAL setting on the server), but this time difference will not accumulate with each consecutive job execution.

HTH,

[hrishy]

hi jurij

could you elaborate what is anchoring of time here means..................


reagrds
hrishy

[jmodic]

Let's say on your system you have only one job queue defined (JOB_QUEUE_PROCESSES=1) and this job queue wakes up every 5 minutes (JOB_QUEUE_INTERVAL=300).

You now want to schedule your job to execute each day at noon (12:00). So you schedule your job with those parameters:
next_date => TO_DATE('25-MAY-2001 12:00','DD-MON-YYYY HH24:MI')
interval => 'SYSDATE + 1'

So idealy your job will be first started today at noon. But as your job queue process wakes up only every 5 minutes, your job might actually start at any time between 12:00 and 12:05. Let's suppose jour job is actually started today with 4 minutes delay at 12:04. At the moment the job is actualy started, the system also calculates the starting date for the next job execution. Based on INTERVAL parameter it determine the next time: exactly 1 day after this moment, which actually means tomorrow at 12:04 and not at 12:00 as you wanted!

So tomorow the job might be started any time between 12:04 and 12:09. If it actually starts tomorrow at 12:09, the next run for the day after tomorrow wil be sceduled at 12:09, but might actually start at 12:14 and so on and so on. So after one month you'll find your job to be started at 14:00 instead of 12:00.

To avoid this you can "anchor" your interval on a fixed time each day, not on last execution time. So if instead of using

interval => 'SYSDATE + 1' , where next time is calculated as "start time today + one day"

you use

interval => 'TRUNC(SYSDATE+12/24) + 1', the system will calculate the next time as "today noon + one day", so the job will start each day sometime between 12:00 and 12:05, no earlier or no later than this.


[Edited by jmodic on 05-25-2001 at 07:31 AM]

[hrishy]

hi jurij

hey dude i think with an explanation as crystal clear as that i have an advice why dont you write a book ..........

thanks a lot for explaining and taking the trouble.......

regards
hrishy

[nalanda]

Jurij,
Thank you verymuch for explaining as to what is happening with regard to the DBMS_JOB.

But this still doesn't work
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT (:jobno,
'job_ops_Station_hourly_yield;',
SYSDATE , 'TRUNC(SYSDATE, 'HH') + 1/24' );
COMMIT;
END;
/

Any suggestions.....
Thanks

[jmodic]

You are having troubles because PL/SQL parser doesn't correctly recognise your single quotes around HH inside your interval parameter string. The following should do it:

DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT (
jobno,
'job_ops_Station_hourly_yield;',
SYSDATE ,
'TRUNC(SYSDATE, ''HH'') + 1/24'
);
COMMIT;
END;
/

[nalanda]

Jurij,
Thanks you very much.
That works.

[jm]

how you test?

try display the result, jmodic's formula is correct:

SQL> select to_char(sysdate,'MM-DD:HH24:MI:SS') from dual;

TO_CHAR(SYSDAT
--------------
05-25:10:04:23

SQL> select to_char(trunc(sysdate,'HH')+1/24,'MM-DD:HH24:MI:SS') from dual;

TO_CHAR(TRUNC(
--------------
05-25:11:00:00

it add one hour on the hour!!!

[kris109]

I agree. Jmodic should write a book.