Interesting bug, isn't it?
If you really needed to use dbms_lock.sleep, you would need
to write a wrapper that multiplies the oriiginal time by 0.976, then
breaks the time into chunks, say 100 second chunks, plus the
remainder, and calls dbms_lock.sleep for each chink.
There would be some overhead, but not much, and it would
still be much more accurate than the standard call.
Something like this:
create or replace procedure accu_sleep ( seconds_in number )
is
v_chunk_size constant integer := 100;
v_compensation constant number := 0.976;
v_chunks integer;
v_remainder integer;
v_seconds integer;
begin
v_seconds := seconds_in;
v_chunks := trunc(v_seconds/v_chunk_size);
v_remainder := mod(v_seconds, v_chunk_size);
for i in 1..v_chunks
loop
dbms_lock.sleep(v_chunk_size);
end loop;
dbms_lock.sleep(v_remainder);
end;
... might work. haven't tested yet.
Jared
On Friday 01 November 2002 07:25, Jamadagni, Rajendra wrote:
> Jared,
>
> I didn't know this one ...
>
> Thanks
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of ESPN
> Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
>
>
> -----Original Message-----
> From: Jared Still [mailto:jkstill@;cybcon.com]
> Sent: Friday, November 01, 2002 10:20 AM
> To: Jamadagni, Rajendra; '[EMAIL PROTECTED]'
> Subject: Re: dbms_lock.sleep irregularities
>
>
>
> Raj,
>
> Actually, that isn't the problem. The to_date is unnecessary, but if
> you examine my example you will see that the correct number of
> seconds is calculated.
>
> The actual problem is described at :
>
> http://www.jlcomp.demon.co.uk/faq/sleep.html
>
> Thanks to Waleed to locating that.
>
> The short version is this, dbms_lock.sleep is inaccurate by 2.4%
> up to 2100 seconds, and unusable for any value over 2097 seconds.
>
> Jared
>
> On Friday 01 November 2002 05:01, Jamadagni, Rajendra wrote:
> > Jared,
> >
> > remove the to_date ... that is causing the problem ...
> >
> > =============== DOESN'T WORK =============================
> > oraclei@pallas-ACPT2> sys
> >
> > SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 1 07:51:00 2002
> >
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> > Connected.
> > SQL> set serveroutput on
> > SQL> @r2
> >
> > SYSTIMESTAMP
>
> ---------------------------------------------------------------------------
>
> > 01-NOV-02 07.51.06.277290 AM -05:00
> >
> > -28266
> >
> > PL/SQL procedure successfully completed.
> >
> >
> > SYSTIMESTAMP
>
> ---------------------------------------------------------------------------
>
> > 01-NOV-02 07.51.06.347844 AM -05:00
> >
> > SQL> get r2
> > 1 select systimestamp from dual
> > 2 /
> > 3 declare
> > 4 seconds integer;
> > 5 begin
> > 6 -- seconds from now til 08:00 AM
> > 7 select (to_date(trunc(sysdate)+(8/24)) - sysdate ) * ( 24*60*60)
> > 8 into seconds
> > 9 from dual;
> > 10 dbms_output.put_line(seconds);
> > 11 dbms_lock.sleep(seconds);
> > 12 end;
> > 13 /
> > 14* select systimestamp from dual
> > SQL> exit
> > ================== END =================================
> >
> > and
> >
> > =============== WORKS FINE ===============================
> > oraclei@rhea-ACPT1> sys
> >
> > SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 1 07:50:24 2002
> >
> > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> > Connected.
> > SQL> set serveroutput on
> > SQL> @r1
> >
> > SYSTIMESTAMP
>
> ---------------------------------------------------------------------------
>
> > 01-NOV-02 07.50.36.239584 AM -05:00
> >
> > 564
> >
> > PL/SQL procedure successfully completed.
> >
> >
> > SYSTIMESTAMP
>
> ---------------------------------------------------------------------------
>
> > 01-NOV-02 08.00.13.854655 AM -05:00
> >
> > SQL> get r1.sql
> > 1 select systimestamp from dual
> > 2 /
> > 3 declare
> > 4 seconds integer;
> > 5 begin
> > 6 -- seconds from now til 08:00 AM
> > 7 select ((trunc(sysdate)+(8/24)) - sysdate ) * ( 24*60*60)
> > 8 into seconds
> > 9 from dual;
> > 10 dbms_output.put_line(seconds);
> > 11 dbms_lock.sleep(seconds);
> > 12 end;
> > 13 /
> > 14* select systimestamp from dual
> > ================== END =================================
> > Raj
> > ______________________________________________________
> > Rajendra Jamadagni MIS, ESPN Inc.
> > Rajendra dot Jamadagni at ESPN dot com
> > Any opinion expressed here is personal and doesn't reflect that of ESPN
> > Inc.
> >
> > QOTD: Any clod can have facts, but having an opinion is an art!
>
> ----------------------------------------
> Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> Content-Transfer-Encoding: 7bit
> Content-Description:
> ----------------------------------------
>
> ----------------------------------------
> Content-Type: text/plain; charset="iso-8859-1"; name="ESPN_Disclaimer.txt"
> Content-Transfer-Encoding: 7bit
> Content-Description:
> ----------------------------------------
----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:
----------------------------------------
----------------------------------------
Content-Type: text/plain; charset="iso-8859-1"; name="ESPN_Disclaimer.txt"
Content-Transfer-Encoding: 7bit
Content-Description:
----------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
