SQL & PL/SQL

SLEEP Procedure
This procedure suspends the session for a given period of time.

Syntax

DBMS_LOCK.SLEEP (
seconds IN NUMBER);
Parameters

Table 53-11 SLEEP Procedure Parameters

Parameter Description
seconds
Amount of time, in seconds, to suspend the session.

The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value.

Hi Hua,

Yes, I am aware about the description. Can you please suggest on the second part of the question.

Thanks,
Shaz

Where have you read that it is not accurate? What accuracy do you need? Second? Decimal seconds? Milliseconds?

Even if there is a time calculation anomaly (e.g. Oracle using 100th of a second and you wanting a finer grain calculation), it will make negligible difference over a period of 15 minutes.

Summary of DBMS_LOCK Subprograms
Table 53-3 DBMS_LOCK Package Subprograms

Subprogram Description
ALLOCATE_UNIQUE Procedure
Allocates a unique lock ID to a named lock.

CONVERT Function
Converts a lock from one mode to another.

RELEASE Function
Releases a lock.

REQUEST Function
Requests a lock of a specific mode.

SLEEP Procedure
Puts a procedure to sleep for a specific time.

PL/SQL lock timer
Definition: This Wait-Event represents the amount of time a user or application has “slept” through the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures.

When investigating Oracle Wait-Events, the lion's share of time is spent looking at those events where applications consume precious system resources or are in direct contention with other users or applications. These resource waits revolve around excessive CPU, memory, or disk usage. Events that spin for Oracle internal structures such as latches or enqueues can also be of concern. As their name suggests, Wait-Events DO ‘wait’ on some event to complete before a user or application can continue with its work. Contention for system resources will overwhelm Oracle’s ability to immediately process the SQL and ultimately cause applications to accumulate time in resource specific Wait-Events.

On the flip-side of resource waits there are what are called idle events. These events do not wait for any specific resource but record the time Oracle is waiting for a work request from the application. Many practitioners of Oracle performance will not even look at these idle events because they do not consume resources and are not limiting what Oracle can do at any given point in time. Some will even go so far as to state that the idle events have no meaningful information. This is not necessarily true. Take for instance the idle event ‘SQL*Net message from client’. This idle event is not inhibiting the database server from performing work but, as many agree, is an indication of poor response from client to database server. While idle events are not the result of direct resource contention, they are an accurate measure of accumulated delays in the application imposed by Oracle.

The ‘idle’ event “PL/SQL lock timer” is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.

While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance.

Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event.

How to look at the PL/SQL lock timer event
To initiate a sleep for the current session for five seconds.


SQL> execute sys.dbms_lock.sleep(5);



To take a look at current sessions that are using the SLEEP command. Notice that the column P1 in V$SESSION does not represent the amount of time the session has slept but in fact represents the duration this session will sleep. Also note that this column is in centiseconds and as such the five seconds issued for a sleep has been translated in to 500 centiseconds.


SQL> select osuser,event,p1 from v$session where event = 'PL/SQL lock timer'


OSUSER EVENT P1
Johnny Smith PL/SQL lock timer 500

HI Billy,

Link has been posted with my question. Please find the same for your reference.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6019886867656

I am using Oracle 10G database.

Thanks,
Shaz

And in that link Tom has provided an alternative approach using a loop and a smaller sleep period if youi're concerned about such inaccuracies.

You haven't mentioned version or any particular detail but I would probably look at usnig a scheduled job to achieve the same.
Depending on version see DBMS_JOB and DBMS_SCHEDULER.

Hi Dom,

Yes thats correct, but I just want to know if the issue persist in Oracle 10G also.

I will check at my end and will advise.

Cheers,
Shaz

Ah.. interesting. Never have used Sleep() for more than a few minutes. And even when for a number of minutes, an inaccuracy of a few seconds are not relevant.

I would expect the problem to be when dealing with smaller than a 100th of second period as that is the smallest grain in Oracle I think.

I honestly do not see a problem here. If the inaccuracy after 15 minutes is that the process slept for 30 minutes, then that is a valid gripe. But that is not the case, or is it?

Keep in mind that highly accurate timing and synchronisation are not features that one would use in SQL and PL/SQL. These s/w layer are too far removed from the kernel and too abstracted from kernel interfaces. And not only that, the issue of real-time processing in such layers are flawed concepts.

So I do not see what you expect the problem to be? Why would your PL/SQL code fail when waiting for 15 minutes to continue, and when continuing to process, the actual time lapse was 5 seconds more than 15 minutes?

There is no issue!

DBMS_LOCK.Sleep() in Oracle is used to spin (wait) a process until it can proceed. E.g. PL/SQL session uses multiple DBMS_JOB processes for as "parallel processing threads" and need to wait for the jobs to complete, before it continues processing:

start jobs 1 to 5
loop
    check if jobs 1 to 5 still exist in USER_JOBS
    exit when no jobs found
    sleep for a few seconds or minutes
end loop
continue processing

What is the purpose of calling DBMS_LOCK.Sleep() ?

The purpose is to off-load processing from the CPU. A very tight loop checking whether those jobs completed and doing that check several 100 times per second, will hit the CPU and database very hard... and totally unnecessary.

So using DBMS_LOCK.Sleep() the loop can sleep for 5 seconds and hit the CPU once per 5 seconds, instead of a 100 times per second. Which means CPU resources now available for others to use.

And this is in a nutshell why DBMS_LOCK.Sleep() is used in PL/SQL code.

It is not a synchronisation interface. It is not intended for use by different processes to go into loops and synchronise execution. That does not make sense inside a database stored proc environment. That needs direct access to the kernel API in order to directly deal with thread and process synchronisation.

Thus, there is no issue with DBMS_LOCK.Sleep() being so "inaccurate" that it it not feasible of being used inside Oracle. It does what it needs to do, correctly. And if you want to argue that this is not the case, I would argue that the design of your Oracle code is wrong and flawed.

See this old thread about this argument.

Note : without reading all the (very long) thread, the sleep procedure is created just using dbms_lock.sleep.

Hi Billy,

Thansk for clarifying the purpose of DBMS_LOCK package. I was not able to find such a clear explanation anywhere. So for my requirement mentioned in the first thread please can you advise which function I should use so that the procedure waits for the flag and should only proceed once it is set.

Thanks,
Shaz

>
So for my requirement mentioned in the first thread please can you advise which function I should use so that the procedure waits for the flag and should only proceed once it is set.
>
Why not use DBMS_SCHEDULER or DBMS_JOB to do this for you?
They don't have a problem with long interval times.

1. Create a job to run with whatever frequence you desire.
2. The job calls your procedure
3. Your procedure checks the field and if not ready just exits - next job execution will call it again.
4. If your procedure checks the field and decides to run let the procedure kill the job if you are done with it.

No need to create your own scheduling. Use the built-in functionality whenever possible.