Re: Execution interval is incorrect while using DBMS

From: Eric Parker <eric.parkerthedross_at_virgin.net>
Date: Thu, 19 Aug 2004 15:49:32 +0100
Message-ID: <2ojsvrFbkosbU1@uni-berlin.de>

"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

Received on Thu Aug 19 2004 - 09:49:32 CDT

Re: Execution interval is incorrect while using DBMS_JOB.SUBMIT