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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
