Oracle-L: RE: dbms_lock.sleep irregularities

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 01 Nov 2002 08:59:48 -0800
Message-ID: <F001.004F9D0A.20021101085948@fatcity.com>

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-----
Sent: Friday, November 01, 2002 10:20 AM To: Jamadagni, Rajendra; 'ORACLE-L_at_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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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).



text/plain attachment: ESPN_Disclaimer.txt

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