Re: time shift in sheduled job

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.