Oracle-L: Re: dbms_lock.sleep irregularities

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 01 Nov 2002 08:59:57 -0800
Message-ID: <F001.004F9D8A.20021101085957@fatcity.com>

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_at_cybcon.com]
> Sent: Friday, November 01, 2002 10:20 AM
> To: Jamadagni, Rajendra; 'ORACLE-L_at_fatcity.com'
> 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_at_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_at_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: jkstill_at_cybcon.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).

Received on Fri Nov 01 2002 - 10:59:57 CST