Library Cache Lock Analysis (vlibcache.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script identifies sessions that are holding library cache locks and blocking other sessions. It:

• Finds sessions waiting on library cache locks
• Identifies the sessions holding those locks
• Shows what the holding sessions are currently doing
• Helps diagnose library cache contention issues

The Script

select sid,substr(event,1,30),wait_time
  from v$session_wait
  where sid in (select sid from x$kglpn , v$session
  where KGLPNHDL in (select p1raw from v$session_wait
  where wait_time=0 and event like 'library cache lock%')
  and KGLPNMOD <> 0
 and v$session.saddr=x$kglpn.kglpnuse )
  /

Usage

-- Basic usage
@vlibcache.sql

-- No parameters required

Required Privileges

SELECT ON V$SESSION_WAIT
SELECT ON V$SESSION
SELECT ON X$KGLPN

Sample Output

       SID SUBSTR(EVENT,1,30)               WAIT_TIME
---------- ------------------------------ ----------
       125 db file sequential read                  0
       248 SQL*Net message from client             -1
       367 PL/SQL lock timer                        0

Key Output Columns

• SID - Session ID of the blocking session
• EVENT - Current wait event of the blocking session
• WAIT_TIME - Wait time status:
  • 0: Currently waiting
  • -1: Last wait was less than 1 centisecond
  • >0: Time of last wait in centiseconds

Common Wait Events for Blocking Sessions

• SQL*Net message from client - Session idle, waiting for client
• db file sequential read - Reading blocks from disk
• PL/SQL lock timer - Executing DBMS_LOCK.SLEEP
• direct path read/write - Performing direct I/O operations

Common Use Cases

Diagnose Compilation Hangs

-- When DDL or package compilation hangs
@vlibcache.sql
-- Identify and potentially kill blocking sessions

Troubleshoot Application Freezes

-- When application seems frozen during deployment
@vlibcache.sql
-- Find sessions preventing code changes

Understanding Library Cache Locks

Common Causes:

1. Long-running procedures being recompiled
2. DDL operations on frequently accessed objects
3. Package state issues requiring recompilation
4. Debugging sessions holding locks

Lock Modes:

• Null (0): No lock
• Shared (2): Can read object definition
• Exclusive (3): Can modify object definition

Troubleshooting Steps

1. Identify blockers using this script
2. Get more details about blocking sessions:

SELECT sid, serial#, username, program, machine, sql_id
FROM v$session
WHERE sid IN (/* SIDs from vlibcache.sql output */);

3. View what objects are locked:

SELECT * FROM v$db_object_cache
WHERE locks > 0 OR pins > 0
ORDER BY locks DESC, pins DESC;

4. If necessary, kill blocking session:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Prevention Strategies

• Avoid compiling objects during peak hours
• Use edition-based redefinition for online changes
• Implement proper error handling to avoid hung sessions
• Set appropriate timeouts for long operations

Related Scripts

• Blocking Locks - General lock blocking analysis
• Active Sessions - View all active sessions
• Compile Invalid Objects - Recompile invalid objects