Re: dbms_lock.sleep irregularities

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

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:

---

-- 
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:45 CST