Re: time shift in sheduled job

From: Frank van Bortel <fbortel_at_home.nl>
Date: Wed, 01 Nov 2000 18:41:31 GMT
Message-ID: <3A004557.3D90467C@home.nl>

[pop!]
how about the job letting schedule itself at the end of the run?

David Fitzjarrell wrote:

> In article <8top5a$eb3$1_at_kermit.esat.net>,
> "Keith Jamieson" <Keith.Jamieson_at_phoenix.ie> wrote:
> > Ift you're on Unix, why don't you just schedule a CRON job to kick
 off your
> > procedure every 2 Hours.
> >
> > Keith Jamieson
> > Phoenix Technology Group
> >
> > James wrote in message ...
> > >Thank you for your input.
> > >
> > >The problem is that the duration will be different every time the
 procedure
> > >runs.
> > >
> > >Also it seems to me that it is not true in my case that "NEXT_DATE
 are
> > >calculated using the value specified in interval and the time the job
> > >ENDED". I have run the job for a few days. Each time the job will
 take a
 few
> > >minutes to 20 minutes ( I have a log to track the duration) to
 finish but
> > >the time shift is about 12 minutes for the past 4 days.
> > >
> > >"David Fitzjarrell" <oratune_at_aol.com> wrote in A wrote:
> > >> > In article <G3B640.7Jw_at_news.boeing.com>,
> > >> > "James" <jialong.x.xie_at_boeing.com> wrote:
> > >> > > I need to run the scheduled job every two hours. So far so
 good.
 But I
> > >> > > notice that there is a problem because the next_date has been
 shifting
 from
> > >> > > the sharp o'clock after a few runs. For example, if I start
 the job
 at
 12:00
> > >> > > noon, the next job will be running at about 2:01, the next one
 may
 be
 4:02.
> > >> > > I can see that this will become serious after a few months. I
 can
 re-submit
> > >> > > the job after a few days. But is there any better way to reset
 the
 next_date
> > >> > > to sharp o'clock? Thanks in advance for any clue.
> > >> > >
> > >> > > Here is the script:
> > >> > >
> > >> > > BEGIN
> > >> > > DBMS_JOB.ISUBMIT
> > >> > > (1,'MY_PROC;',TRUNC(SYSDATE)+12/24,'SYSDATE+1/12',NULL);
> > >> > > COMMIT;
> > >> > > END;
> > >> > >
> > >> > >
> > >> > I haven't tested this, so I don't know if it is entirely
 accurate.
 But,
> > >> > I do know that next_date and next_sec are calculated using the
 value
> > >> > specified in interval and the time the job ENDED, not when it
 began.
> > >> > So, my assumption is that the job runs in less than an hour. Try
 using
> > >> > the following specification for interval.
> > >> >
> > >> > 'to_char(sysdate, 'DD/MM/YYYY:HH24') + 1/12'
> > >> >
> > >> > I believe this will add 2 hours to the 'hour' when the job ended,
> > >> > ignoring the value of 'minutes' altogether.
> > >> >
> > >> > HTH,
> > >> > Patrick
> > >> >
> > >> > Sent via Deja.com http://www.deja.com/
> > >> > Before you buy.
> > >> >
> > >>
> > >> You are accurate about the next time calculation however your
 solution
> > >> will not work as it returns an error from SQL*Plus:
> > >>
> > >>
> > >> SQL> select to_char(sysdate, 'DD/MM/YYYY:HH24') + 1/12
> > >> 2* from dual
> > >> SQL> /
> > >> ERROR:
> > >> ORA-01722: invalid number
> > >>
> > >>
> > >> no rows selected
> > >>
> > >> As I stated in a previous post the time shift constant will need
 to be
> > >> slightly less than 1/12 to compensate for the run time of the job.
> > >>
> > >> --
> > >> David Fitzjarrell
> > >> Oracle Certified DBA
> > >>
> > >>
> > >> Sent via Deja.com http://www.deja.com/
> > >> Before you buy.
> > >
> > >
> > >
> >
> >

>

> There are good and bad aspects of cron.
> The good:

>


> *   Jobs can be scheduled to the minute.

> * The schedule does not drift.
> * Multiple jobs can be scheduled around the same time.

>

> The bad:

>


> *   Jobs requiring Oracle access take a bit longer to execute

>     than Oracle jobs scheduled through DBMS_JOB due to connectivity

>     issues and Oracle session creation.

> *   External Oracle jobs require more resources than internal (DBMS_JOB)

>     jobs.

> *   Environment needs to be set for cron jobs to execute properly, not

>     necessary for internal Oracle jobs.

>

> That being said the choice is really up to the person running the job.
> For repeatable, essentially drift-free schedules cron is the best bet.
> For efficient use of Oracle resources DBMS_JOB is the best method. It
> is unfortunate that DBMS_JOB introduces a bit of 'time creep' in the
> scheduled jobs. Outside of using a shift constant that compensates as
> best as possible for the lag I can find no other solution with DBMS_JOB.

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Gtrz,

Frank van Bortel

Received on Wed Nov 01 2000 - 12:41:31 CST