Re: SLEEP Procs

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1168810651.31712_at_bubbleator.drizzle.com...
> Can't find the original thread but I read Jonathan Lewis' page on SLEEP
> http://www.jlcomp.demon.co.uk/faq/sleep.html and it refers to versions
> 7.3 through 9.2.0.3.
>
> Thus I decided to see where things stand with 10gR2 and found the
> following which I thought I'd share with the group.
>
> set linesize 121
> col package_name format a20
> col argname format a9
> col data_type format a14
> col length format 999
> col prec format 999
> col pls_type format a14
>
> SELECT package_name, argument_name ARGNAME, data_type,
> data_length LENGTH, data_precision PREC, pls_type
> FROM all_Arguments
> WHERE object_name = 'SLEEP'
> ORDER BY 1;
>
> PACKAGE_NAME ARGNAME DATA_TYPE LENGTH PREC PLS_TYPE
> -------------------- --------- -------------- ------ ---- --------------
> DBMS_BACKUP_RESTORE SECS BINARY_INTEGER BINARY_INTEGER
> DBMS_DRS SECONDS NUMBER 22 38 INTEGER
> DBMS_LOCK SECONDS NUMBER 22 NUMBER
> USER_LOCK TENS_OF_M NUMBER 22 NUMBER
> ILLISECS
>
> SQL> DECLARE
> 2 stime TIMESTAMP(9);
> 3 etime TIMESTAMP(9);
> 4 BEGIN
> 5 stime := SYSTIMESTAMP;
> 6 dbms_backup_restore.sleep(0.5);
> 7 etime := SYSTIMESTAMP;
> 8 dbms_output.put_line(etime-stime);
> 9
> 10 stime := SYSTIMESTAMP;
> 11 dbms_drs.sleep(0.5);
> 12 etime := SYSTIMESTAMP;
> 13 dbms_output.put_line(etime-stime);
> 14
> 15 stime := SYSTIMESTAMP;
> 16 dbms_lock.sleep(0.5);
> 17 etime := SYSTIMESTAMP;
> 18 dbms_output.put_line(etime-stime);
> 19
> 20 stime := SYSTIMESTAMP;
> 21 user_lock.sleep(50);
> 22 etime := SYSTIMESTAMP;
> 23 dbms_output.put_line(etime-stime);
> 24 END;
> 25 /
> +000000000 00:00:01.000000000
> +000000000 00:00:00.500000000
> +000000000 00:00:00.500000000
> +000000000 00:00:00.500000000
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> It seems the only one that can't handle a fraction is
> dbms_backup_restore.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

You'll notice that dbms_lock.sleep() now recognises that there are 1,000 milliseconds to a second, rather than 1,024.

Did you check for the "long" sleep problem that the FAQ describes.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html