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

If you wanted to take a look at the total amount of time that has accumulated for this type of wait event, you can look at the V$SYSTEM_EVENT view. Also note that in this particular view the time waited is also in centiseconds but as you can see the time actually waited may be more that expected. On our test system this equated to about 1.024 seconds to 1 second requested.

	SQL> select time_waited from v$system_event where event = 'PL/SQL lock timer';

Conclusion

Even though the 'PL/SQL lock timer' wait event is considered an idle event, ifan application is waiting for it, the end user is also waiting. Our purpose as a DBA is to increase the throughput of the Oracle engine by limiting the amount of time an application runs. It does not matter if an application is spending time in an idle state or contending for resources, we need to identify the wait, determine its impact, and provide an environment that allows database workload to complete within respected times.