The PL/SQL lock timer wait event represents the amount of time a user or application has “slept” through the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures. The ‘idle’ event “PL/SQL lock timer” is worth watching because it can indicate issues with application response, throughput and possibly coding.
The PL/SQL lock timer wait 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 five minutes, the response to the user or complete run of the application will take at least five minutes longer.
Why look at the PL/SQL lock timer wait event?
Most often, Oracle 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 wait 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 Oracle 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';
Final thoughts
Even though the PL/SQL lock timer wait event is considered an idle event, if an application is waiting for it, the end user is also waiting. As a DBA, our job 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. This response time analysis can ensure optimal database performance.