The Oracle (tm) Users' Co-Operative FAQ

Is there a way to suspend a task, or make it sleep, for a period of time ?


Author's name: Jonathan Lewis

Author's Email: jonathan@jlcomp.demon.co.uk

Date written: 24th July 2001
Updated: 2nd Nov 2002
Updated: 9th Aug 2003

Oracle version(s): 7.3 - 9.2.0.3

From time to time, you may want a task to operate in a timed loop, perhaps waiting a few seconds before attempting to repeat an action that has failed, or possibly waiting until a specific point in time to start running. What methods does Oracle supply to make this possible. Note, however, that a couple of bugs, introduced some time around version 8, may cause problems.

Back to index of questions


Latest Update at end:

There are two PL/SQL packged procedures that exist to allow a process to suspend itself temporarily withouth consuming CPU. These are dbms_lock.sleep() and user_lock.sleep() which is simply a layer that superimpsed over dbms_lock.sleep().

The dbms_lock package is created as part of the standard installation when catproc.sql is run. If you system does not have it installed, it can be created by running the script $ORACLE_HOME/rdbms/admin/dbmslock.sql, after connecting to the database under the SYS account. There is a public synonym in place for the package, but under 8.1 execute rights are granted to the role EXECUTE_CATALOG_ROLE, so you may not be able to call the function under a low-privilege account.

The user_lock package is not installed by default and has to be created by SYS by running the script $ORACLE_HOME/rdbms/admin/userlock.sql. This package ends up with a public synonym with execute rights granted to the role PUBLIC.

There is one significant difference between the two packages - dbms_lock.sleep() specifies the sleep time in seconds, user_lock sleep() specifies its sleep time in hundredths of seconds (or as the package code puts it: tens of milliseconds). The granularity, or precision, in both cases is 1/100 sec - the following calls both have the same effect from SQL*Plus:

        execute dbms_lock.sleep(0.05);
        execute user_lock.sleep(5);

The two functions do not consume CPU, so it is perfectly reasonable to use them to kick off a process at fairly regular intervals (but do also look at the dbms_job package for a more traditional approach to scheduling Oracle tasks) without wasting resources:

begin
        loop
               -- do some complicated pl/sql code here
               -- then wait one minute before doing it again
               dbms_lock.sleep(60);
        end loop;
end;
/

An alternative use is to kick of a task at a specific time, by using a future-time calculation. Again you should consider using dbms_job for this task, but in some cases, the degree of accuracy that you can achieve through dbms_job.sleep() may be of value. For example, to start a process at 13:15 - rather than the first time after 13:15 that a job queue process (i.e. snp0 or one of its peers) wakes up:

declare
        m_count_down number;
begin
        select 
               86400 * (
                       to_date(
                               to_char(trunc(sysdate),'yyyymmdd')||'1315',
                               'yyyymmddhh24mi'
                       ) - sysdate
               )
        into m_count_down
        from dual;
 
        dbms_lock.sleep(m_count_down);
 
        --  do something at 13:15 - or straight away if 13:15 has already passed
 
end;
/

Note that dbms_lock.sleep() and user_lock.sleep() do not wait if the input parameter is negative.


BUG Warning:

My thanks to Jurij Modic for pointing out the following problems with the dbms_lock package in Oracle 8.1 (and possibly earlier versions). First, the timer overruns by 2.4% (plus or minus the odd centisecond due to CPU scheduling effects). This has been recorded as bug 1522119 - which is not visible on Metalink. Personally I attribute it to a numeric conversion issue - someone in Oracle Corp. has assumed that there are 1,024 milliseconds in a second ;)

The second bug is perhaps more serious - there is an overflow error around 2,097 seconds (or, on some platforms which use an unsigned word, 4,194 seconds) which makes all attempts to set a longer time-out go seriously wrong (Bug 1842189 applies). Jurij Modic sent me the following sample of times to demonstrate the issue.

SLEEP_REQUESTED SLEEP_ELAPSED
--------------- -------------
           2100       2144.58
           2200       2042.18
           2300       1939.77
           2400       1837.37
           2500       1734.98
           3000       1222.96
           4000        198.97
           5000        825.03
           6000       1849.05
           7000       1421.94
           8000        397.93
           9000        626.08
          10000       1650.08
          11000       1620.91
          12000        596.91
          13000        427.11
          14000       1451.10
          15000       1819.88

Be warned - dbms_lock() at the latest release is predictably inaccurate up to 2,100 seconds, and thereafter unusable. There is no fix recorded as yet for either of these bugs. (Anyone who can test on 9.0 is invited to report their results)


Update 2nd Nov 2002 from Jared Still

One option for dealing with the problem of long sleep times is to break the sleep down into units that are short enough to bypass the problem. Jared Still has supplied the following example of a procedure to do this. Note that the procedure also takes care of the 2.4% error reported above.:

        create or replace procedure accusleep ( seconds_in number )
        is
           v_chunk_size constant integer := 100;
--         v_compensation constant number := 0.976;
--
--      Modification to compensation factor suggested by Jeffe Legge May 2003
--
           v_compensation constant number := 1000/1024;
           v_chunks integer;
           v_remainder integer;
           v_seconds integer;
        begin
           v_seconds := seconds_in * v_compensation;
           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);
           --dbms_output.put_line(v_chunks);
           --dbms_output.put_line(v_remainder);
        end;
        /

And a demonstration of its use.

        declare
           seconds integer;
        begin
           -- seconds from now til 10:00 AM
           select (to_date(trunc(sysdate)+(10/24)) - sysdate ) * ( 24*60*60) into
        seconds from dual;
           dbms_output.put_line(seconds);
           accusleep(seconds);
        end;
        /

Update 9th Aug 2003 from Jeff Legge

Jeff Legge has suggested that for higher precision the code supplied by Jared Still could use an exact conversion factor, as shown above, and offers the following (unix based) test to show that the bug is still present in 9.2

.jeffl@mycroft:~/SQL$ time sqlplus -S system/****** << _EOF_
> exec dbms_lock.sleep(1000);
> _EOF_


With the result:

PL/SQL procedure successfully completed.

real    17m4.108s
user    0m0.020s
sys     0m0.000s
jeffl@mycroft:~/SQL$


Update 24th Jan 2005 from Chris Poole

On 13th Jan, Chris Poole dropped me an email to say that he had found an alternative solution to the buggy DBMS_LOCK.SLEEP documented in this FAQ.  The answer is posted on his website in the tips section and involves a package that, funnily enough, he first found out about from the Co-operative Oracle Users’ FAQ site.


Further reading: N/A


Back to top

Back to index of questions