Re: Execution interval is incorrect while using DBMS_JOB.SUBMIT

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 20 Aug 2004 10:08:16 +0200
Message-ID: <cg4b59$13c$1@news5.tilbu1.nb.home.nl>

Eric Parker wrote:

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1092922159.408784_at_yasure...
>

>>Eric Parker wrote:
>>
>>
>>>"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message
>>>news:cg24p2$l3m$1_at_news2.tilbu1.nb.home.nl...
>>><snip>
>>>
>>>>What Jim and Eric said.
>>>>And the reason is that Oracle reschedules the job _after_
>>>>it's completed, so if your schedule is "5 minutes later", your
>>>>job will rerun 5 minutes after *completion* of the previous run.
>>>>--
>>>>
>>>>Regards,
>>>>Frank van Bortel
>>>>
>>>
>>>
>>>Frank
>>>
>>>Its my understanding that Oracle calculates the interval prior to

>
> executing
>

>>>the job. If the job completes successfully then that value is used to
>>>schedule
>>>the next run otherwise it goes into its wait for an increasing period

>
> try
>

>>>again
>>>loop.
>>>
>>>eric
>>
>>Not correct. Take a look at the dba_jobs view.
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)
>>

>
>
> Daniel
>
> Maybe my words were a bit woolly but lets give it another go.
>
> My understanding and the behaviour I see is that prior to
> execution of DBMS_JOB.SUBMIT's WHAT parameter the
> INTERVAL is evaluated to produce a timestamp for a
> potential next run. The WHAT parameter is then run. If and
> when that returns without error the timestamp is used for
> the next run.
>
> I have just created a procedure that sleeps 90 seconds then
> records the time. I have just invoked this from a job that
> intervals at 'SYSDATE + 3/(24*60)' stated at 15:10. 3 minutes
> I believe.
>
> SQL> SELECT TO_CHAR(TD, 'HH24:MI:SS') FROM TIMEDATE ORDER BY TD;
>
> TO_CHAR(
> --------
> 15:11:36
> 15:14:41
> 15:17:43
> 15:20:48
> 15:23:50
> 15:26:55
> 15:29:57
> 15:33:03
>
> 8 rows selected.
>
> Execution interval between 3 minutes 2 seconds and 3 minutes
> 6 seconds. If it was as Frank said the interval would be just over
> 4 minutes 30 seconds.
>
> The total_time in user jobs is 737 seconds. Just over 90 seconds
> per iteration.
>
> I submitted it with
>
> DECLARE
> X NUMBER;
> BEGIN
> SYS.DBMS_JOB.SUBMIT
> ( job => X
> ,what => 'RECORDTIMESTAMP;'
> ,next_date => to_date('19/08/2004 15:10:00','dd/mm/yyyy hh24:mi:ss')
> ,interval => 'SYSDATE + 3/(24*60)'
> ,no_parse => TRUE
> );
> END;
>
> The procedure is
>
> CREATE OR REPLACE PROCEDURE RecordTimeStamp IS
> BEGIN
> DBMS_LOCK.SLEEP(90);
> INSERT INTO TIMEDATE VALUES (SYSDATE);
> COMMIT;
> END RecordTimeStamp;
>
> Maybe we're talking at cross purposes here.
>
> eric
>
> --
> Remove the dross to contact me directly
>
>

You are correct; I'm wrong. Another "thruth" bites the dust...

The legend could hold because intervals, that do not deliver an exact date ('SYSDATE + 1' vs 'TRUNC(SYSDATE) + 10/24') are bound to skew over a period of time.

-- 

Regards,
Frank van Bortel

Received on Fri Aug 20 2004 - 03:08:16 CDT