Skip to content
Oracle DBA Scripts & Database Utilities

Library Cache Lock Analysis (vlibcache.sql)

What This Script Does

Section titled “What This Script Does”

This script identifies sessions that are:

  • Currently holding library cache locks
  • Blocking other sessions waiting for library cache access
  • Shows the current wait event and wait time for blocking sessions

Script

Section titled “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

Section titled “Usage”
SQL> @vlibcache.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$SESSION_WAIT
  • SELECT on V$SESSION
  • SELECT on X$KGLPN (requires SYSDBA or specific grants)

Sample Output

Section titled “Sample Output”
       SID SUBSTR(EVENT,1,30)              WAIT_TIME
---------- ------------------------------ ----------
       156 db file sequential read                 0
       234 SQL*Net message from client            -1
       512 log file sync                           0

Key Output Columns

Section titled “Key Output Columns”
  • SID: Session ID of the blocking session
  • EVENT: Current wait event of the blocking session (truncated to 30 chars)
  • WAIT_TIME: Wait time status:
    • 0: Currently waiting
    • -1: Last wait was short (< 1 centisecond)
    • -2: Waited unknown time

    • 0: Last wait time in centiseconds

Understanding Library Cache Locks

Section titled “Understanding Library Cache Locks”

Common Causes

Section titled “Common Causes”
  1. DDL Operations: ALTER, DROP, CREATE statements
  2. Compilation: Procedures/packages being compiled
  3. Invalidations: Objects becoming invalid
  4. Hard Parsing: Complex SQL requiring library cache access

Lock Modes

Section titled “Lock Modes”
  • Share: Allows concurrent access for execution
  • Exclusive: Blocks all other access (DDL operations)

Resolution Steps

Section titled “Resolution Steps”
  1. Identify blocking session using this script
  2. Check what the blocking session is doing
  3. Wait for operation to complete or kill session if necessary
  4. Consider scheduling DDL during maintenance windows

Common Use Cases

Section titled “Common Use Cases”

  1. Troubleshooting Hangs

    • Application timeouts during deployments
    • Sessions hanging on object access
    • Performance degradation during DDL

  2. Deployment Planning

    • Identify potential blocking scenarios
    • Plan DDL execution order
    • Minimize library cache contention

  3. Performance Analysis

    • Detect excessive parsing
    • Find objects with high contention
    • Optimize application design

Troubleshooting Tips

Section titled “Troubleshooting Tips”

If no rows returned but library cache waits exist:

  • The lock may have been released
  • Check V$SESSION_WAIT directly for current waits
  • Review alert log for related errors

For persistent issues:

  • Enable library cache debugging
  • Collect AWR/ASH reports
  • Consider increasing shared pool size
Section titled “Related Scripts”