Re: dbms_job interval 10till

Barbara Baker wrote:
>
> Sun / Solaris 2.6 ; Oracle RDBMS v8.0.5.2.1
>
> List:
> I want to change the perfstat statspack.snap procedure
> FROM running every hour on-the-hour
> TO running at 10 minutes till the hour.
> (On-the-hour is conflicting with another procedure.)
> I tried to Read The Frustrating Manual, but I can only
> find examples for on-the-hour intervals.
>
> I thought I had it nailed when I spoon-fed the job
> with the next_date value at 10 till, and then the
> interval for 'SYSDATE+1/24'. When I looked at it
> after the next run, it was fine, but it has "creeped".
> Now it's running whenever it feels like it.
>
> I've lost track of all the date intervals I tried, but
> here's what I tried last.
>
> variable jobno number;
> begin
> select 1 into :jobno from dual;
> end;
> /
> begin
> dbms_job.change(:jobno,
> 'statspack.snap;',
> to_date('09-JAN-2003 9:50') ,
> 'SYSDATE+1/24'
> );
> commit;
> end;
> /
>
> prompt Job number for automated statistics collection
> for this instance
> prompt
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> prompt Note that this job number is needed when
> modifying or removing
> prompt the job:
> print jobno
>
> Here's what it currently looks like:
>
> What: statspack.snap;
> Job: 1
> Priv User: PERFSTAT
> Last: 01/10/2003 14:16:13
> Running: NO
> Next: 01/10/2003 15:16:13
> Interval: SYSDATE+1/24
> Total Time: 53985.9999999999999999999999999999999999
> Broken:
>
> Any idea how I can get to run consistently at 10 till
> each hour?
>
> Thanks in advance for any help.
> Barb
>

Barbara,

  Jobs creep because Oracle polls for jobs every minute by default - more or less. Then it computes 'next run' time, runs the job and, if it works, updates next_date. The problem is that you job starts say only 20 seconds late (based on the 'theoretical' time) this lag accumulates if you compute the next time as SYSDATE + 1/24. Assume 20 s each time on average, it's 8 minutes every day - quickly becomes quite noticeable. The only way to keep things under check is to use TRUNC() to the suitable unit. You should define INTERVAL as

    TRUNC(SYSDATE, 'HH24') + 1/24 + 10/1440

     ^ round time (current)
                              ^ plus one hour
                                     ^ plus ten minutes

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).