PL/SQL lock timer wait event in Oracle Database

PL/SQL lock timer wait event in Oracle occurs when a session is waiting for a lock held by another session to be released. This can happen when multiple sessions are trying to update or modify the same data simultaneously. This event is called through the DBMS_LOCK.SLEEP procedure. This event will most likely originate from procedures written by a user.

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. This event does not require resources and thus is not an Oracle resource contention issue.

To identify the sessions that cause PL/SQL lock timer wait events in an Oracle database, you can use several system views and dynamic performance views, such as v$session_wait and v$session, that provide information about the sessions and their current wait events.

Here are the steps to identify the sessions that cause PL/SQL lock timer wait events:

Query the v$session_wait view to find the sessions that are currently waiting for a lock.

SELECT sid, event, p1, p2, p3 
FROM v$session_wait 
WHERE event = 'PL/SQL lock timer' 
ORDER BY sid;

The column sid in the above query will give you the session id of the session that is waiting for a lock.

Use the session id to query the v$session view to find more information about the session, such as the username and the SQL statement that is being executed.

SELECT sid, username, sql_id, machine 
FROM v$session 
WHERE sid = <sid>;

The column SQL_ID in the above query will give you the id of the sql statement that is being executed by the session. you can use the SQL_ID to find the sql statement using v$sql view

SELECT sql_text 
FROM v$sql 
WHERE sql_id = <sql_id>;

You can also use DBA_BLOCKERS and DBA_WAITERS views to find more information about the sessions involved in the lock and the resources that are being locked

After Identifying the session, you can use the ALTER SYSTEM KILL SESSION command to kill the session and release the lock.

Note that it’s not always necessary to kill the session, it’s better to analyze the root cause of the lock and try to resolve the issue by optimizing the SQL query or increasing the resources, or increasing the timeout of the lock to avoid the session to wait forever.

Single SQL script that you can use to identify the sessions that cause PL/SQL lock timer wait events in an Oracle database:

WITH lock_sessions AS (
SELECT sid, event, p1, p2, p3, 
ROW_NUMBER() OVER (PARTITION BY p1,p2,p3 ORDER BY sid) rn
FROM v$session_wait
WHERE event = 'PL/SQL lock timer'
),
session_info AS (
SELECT s.sid, s.username, s.machine, s.sql_id, s.program, 
q.sql_text
FROM v$session s
JOIN lock_sessions ls ON s.sid = ls.sid
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE ls.rn = 1
)
SELECT sid, username, machine, sql_id, program, sql_text
FROM session_info;

This script uses a combination of the v$session_wait, v$session, and v$sql views to gather information about the sessions that are waiting for a lock and the SQL statements that are being executed by those sessions.

Note: In case of RAC use GV$ instead of V$.

Another query to find the details of sessions with “PL/SQL lock timer” waits can be queried using the following SQL:

SELECT
s.sid,
s.seconds_in_wait,
s.wait_class,
s.event,
s.p1text,
s.p1,
s.p1raw,
s.serial#,
s.program,
s.client_identifier,
s.terminal,
s.command,
ct.command_name,
s.service_name,
s.module,
s.action,
s.username,
s.machine,
s.sql_id,
st.sql_text, -- just the first 64 characters of the SQL text
st.hash_value
FROM v$session s,
v$sqltext st,
v$sqlcommand ct
WHERE st.sql_id (+) = s.sql_id
AND st.piece (+) = 0
AND ct.command_type (+) = s.command
AND s.event = 'PL/SQL lock timer'
ORDER BY s.seconds_in_wait DESC;

When ‘PL/SQL Lock Timer’ wait contention is seen due to user-written procedures there are several ways the issue can be mitigated/resolved by the developer by making changes at code level :

Optimize the SQL queries: If the procedures contain complex or poorly-written SQL queries, it can cause lock contention and result in ‘PL/SQL Lock Timer’ wait events. Identify the slow-running queries and optimize them using techniques such as indexing, partitioning, and rewriting the queries.
Increase lock timeout: You can increase the lock timeout value to allow the session to wait longer for the lock. However, this should be used as a last resort as it can lead to increased wait times and decreased performance.
Use the “NOWAIT” option: Use the “NOWAIT” option in the “SELECT FOR UPDATE” statement to avoid waiting for the lock. This option tells Oracle to return an error if the lock cannot be acquired immediately, rather than waiting for the lock to be released.
Refactor the Procedures: Procedures that have contention on a specific table or object can be refactored to reduce contention by breaking the procedure into smaller procedures or by reducing the number of times the table is accessed.
Use the “SKIP LOCKED” option: Use the “SKIP LOCKED” option in the “SELECT FOR UPDATE” statement to avoid waiting for the locked rows. This option tells Oracle to skip locked rows and return only the unlocked rows.
Monitor and Troubleshoot: Monitor the performance of the procedures using tools such as Oracle’s Automatic Workload Repository (AWR) and Active Session History (ASH) to identify the root cause of the contention.

Reference:

Oracle E-Business Suite Performance Guide (Doc ID 1672174.1)
How To Identify Sessions Which Cause PL/SQL LOCK TIMER Wait Event (Doc ID 434265.1)
Resolving Issues Where ‘PL/SQL Lock Timer’ Wait contention is See Due to User Written Procedures (Doc ID 1476088.1)

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

PL/SQL lock timer wait event in Oracle Database

Related posts: