Does sone have his jobs executed at the right time ???

[pipo]

Here is the problem : I have developped lots of jobs (using dbms_jobs package), and though I used PRECISE interval definitions (like trunc(sysdate)+10/24 for 10AM), I saw that my jobs were executed later and later, the gap was increasing between the launch time and the scheduled one !!
So I am wondering if any of the fabulous actors of this forum know HOW to declare a job for it to be launched when scheduled ???
Thanks for any idea

[kgb]

Hi, I guess that is connected to mathematical accuracy. Just because 10/24=0,41666666666666666666666666666667
This accuracy is accumulated the more times job executed the more accuracy. If you take 12/24 you will not get this problem. Because 12/24=0.5
Best wishes!
Dmitri.

Originally posted by pipo
interval definitions (like trunc(sysdate)+10/24 for 10AM),

[Edited by kgb on 06-21-2001 at 04:32 AM]

[akkerend]

Do you have more jobs to be scheduled at a time, than there job processes? Or are there long running jobs executed before these jobs?

The job processes execute the jobs, the number is specified in the job_queue_processes parameter in your init.ora. When a job process wakes up, it checks the job queue to see if sysdate exceeds the next execution date for any job. If a job is to be executed the process creates a session and executes the pl/sql code. When there is no job to be executed the process sleeps for the time specified in the job_queue_interval parameter.

Do you use trunc(sysdate)+10/24 or trunc(sysdate+1) +10/24?

[pipo]

I don't agree, if you select sysdate+10/24, you'll get exactly sysdate + 10 hours, 10/24 of a day is 10 hours, and there is no problem with that on Oracle.
The interval of a job is evaluated at launch time, so if a job is to be executed at 10AM for example, sysdate+10/24 would evaluate 8PM exactly.

[pipo]

I have more processes (5) than the max number of process that can be executed at a time (4), the process left is here in case of a manual launch.
the job_queue_interval is of 1 minute, which is small enough ...

[akkerend]

If the next_date is trunc(sysdate)+10/24, the job is scheduled in the past and therefore executed over and over again.

[pipo]

I issue a decode on current time to know when the job is to be rescheduled, of course I do not have my job always executed at the same time, I think I would have noticed it

[akkerend]

So next_date in user_jobs is not the current day?

[pipo]

since it doesn't seem to be clear, here is a table :

TEST_JOB (A DATE);

a procedure :

procedure test_job_proc as
begin
insert into test_job values (sysdate);
commit;
end;

and a job :

begin
dbms_job.submit(:jobno,
'TEST_JOB_PROC;',
sysdate,
'sysdate+5/1440');
end;
/

So this job should be launched every 5 minutes, and since next_date is evaluated when job is launched, result should be lines with exactly 5 minutes of difference, nevertheless here is what I get :

21/06/2001 12:37:39
21/06/2001 12:42:46
21/06/2001 12:47:54
21/06/2001 12:53:01
21/06/2001 12:58:08
21/06/2001 13:03:15
21/06/2001 13:08:23
21/06/2001 13:13:30
21/06/2001 13:18:37
21/06/2001 13:23:44
21/06/2001 13:28:52
21/06/2001 13:33:59
21/06/2001 13:39:06
21/06/2001 13:44:13
21/06/2001 13:49:21
21/06/2001 13:54:28
21/06/2001 13:59:35
21/06/2001 14:04:43
21/06/2001 14:09:50

there is a 7/8 seconds difference each time ...
So my questions are : WHY ?? and HOW CAN I DO ???

[jm]

sorry I don;t remeber the thread# but there is a very very nice discussion around 5/25 and JModic had a wonder explaination and solution.