Sleep Function in Oracle

Sleep function in ORACLE

Short of granting access to DBMS_LOCK.sleep, this will work but it's a horrible hack:

IN_TIME INT; --num seconds
v_now DATE;

-- 1) Get the date & time 
SELECT SYSDATE 
  INTO v_now
  FROM DUAL;

-- 2) Loop until the original timestamp plus the amount of seconds <= current date
LOOP
  EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;

Is the PL/SQL function dbms_lock.sleep Safe?

DBMS_LOCK.SLEEP does not consume any important resources and is perfectly safe to use.

I've used it many times and never experienced any problems. The below test creates a massive amount of sleeping jobs but does not cause any problems.

---

To test the SLEEP function, create a huge amount of jobs that all sleep at the same time.

First, make sure that the database can support a large number of jobs. There are several parameters that may limit this. Check the below parameters:

select name, value
from v$parameter
where name in ('job_queue_processes', 'processes', 'sessions')

Create 1000 scheduled jobs that wait 30 seconds.

begin
    for i in 1 .. 1000 loop
        dbms_scheduler.create_job(
            job_name => 'JOB_'||i,
            job_type => 'PLSQL_BLOCK',
            start_date => systimestamp,
            enabled => true,
            job_action => 'begin dbms_lock.sleep(30); end;'
        );
    end loop;
end;
/

Now see how many jobs are running:

select count(*) from gv$session where schemaname = user;
select count(*) from dba_scheduler_running_jobs where owner = user;

On my desktop I only get 239 jobs. It's not 1000 because of the original parameters, but I would consider 239 to still be a good "large" value.

Even with all that activity I don't notice any performance issues and Oracle uses less than 1% of the CPU.

Wait/Sleep in PLSQL

DBMS_LOCK.sleep will put the session process to sleep, i-e: this session will do nothing for 60 seconds.

I'm not sure what you mean by a "lock on connection". If the session that runs the sleep procedure has any lock pending, they will be kept during the sleep and other sessions that might be blocked by it will have to wait for the first session to either commit or rollback as usual.

---

Related Topics