Oracle Library Cache Lock - Diagnose & Resolve Contention

Oracle Library Cache Lock - Diagnose & Resolve Contention

Library cache locks and pins are among the most disruptive wait events in an Oracle database. They surface as application hangs, runaway parse times, and cascading session blockage. This guide explains the mechanics behind these waits, how to identify the root cause quickly, and what to do about it.

Overview

Library Cache Architecture

The library cache is a component of the shared pool that stores parsed SQL, PL/SQL, and object definitions. Before a session can execute a SQL statement or reference a PL/SQL object, it must acquire a lock and pin on the relevant library cache object.

• Library Cache Lock - Serialises access to an object handle. Held in shared mode during execution, exclusive mode during compilation or DDL.
• Library Cache Pin - Controls access to the heap (the compiled code or execution plan). Shared during execution, exclusive during recompilation.
• Library Cache Load Lock - Acquired when loading an object into the library cache for the first time.

-- Current library cache wait events in V$SESSION
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.event,
  s.wait_class,
  s.seconds_in_wait,
  s.p1raw,
  s.p2raw,
  s.sql_id,
  s.blocking_session,
  s.blocking_session_status
FROM v$session s
WHERE s.event IN (
  'library cache lock',
  'library cache pin',
  'library cache load lock'
)
ORDER BY s.seconds_in_wait DESC;

Lock vs Pin: What Is the Difference?

Aspect	Library Cache Lock	Library Cache Pin
Protects	Object handle	Object heap (code)
Shared mode	Parse / execute	Execute
Exclusive mode	DDL / drop / alter	Recompile / invalidate
Typical cause	DDL against active object	Package recompilation
V$SESSION event	library cache lock	library cache pin

---

Diagnosing Library Cache Contention

Step 1 - Identify Waiting Sessions

-- All sessions waiting on library cache events with full context
SELECT
  w.sid,
  w.serial#,
  w.username,
  w.event,
  w.seconds_in_wait                         AS wait_secs,
  w.blocking_session                        AS blocker_sid,
  w.blocking_session_status,
  w.sql_id,
  w.program,
  w.module,
  w.action,
  w.machine,
  TO_CHAR(w.logon_time, 'DD-MON HH24:MI:SS') AS logon_time
FROM v$session w
WHERE w.wait_class != 'Idle'
  AND w.event IN (
        'library cache lock',
        'library cache pin',
        'library cache load lock',
        'cursor: pin S wait on X',
        'cursor: mutex X'
      )
ORDER BY w.seconds_in_wait DESC;

Step 2 - Identify the Blocking Session

The session holding the library cache lock exclusively is usually running DDL, compiling PL/SQL, or has a stale handle open.

-- Blocking session detail - who is causing the queue?
SELECT
  b.sid                                      AS blocker_sid,
  b.serial#                                  AS blocker_serial,
  b.username                                 AS blocker_user,
  b.status                                   AS blocker_status,
  b.event                                    AS blocker_wait,
  b.sql_id                                   AS blocker_sql_id,
  b.prev_sql_id                              AS blocker_prev_sql,
  b.program,
  b.module,
  b.machine,
  b.osuser,
  TO_CHAR(b.logon_time, 'DD-MON HH24:MI:SS') AS logon_time,
  COUNT(w.sid)                               AS sessions_blocked
FROM v$session b
JOIN v$session w
  ON w.blocking_session = b.sid
WHERE w.event IN (
        'library cache lock',
        'library cache pin',
        'library cache load lock'
      )
GROUP BY
  b.sid, b.serial#, b.username, b.status, b.event,
  b.sql_id, b.prev_sql_id, b.program, b.module,
  b.machine, b.osuser, b.logon_time
ORDER BY sessions_blocked DESC;

Step 3 - Identify the Contended Object

The P1 parameter in library cache waits contains a pointer to the object handle. Use V$MUTEX_SLEEP_HISTORY or join through V$LIBCACHE_LOCKS (19c+) to find the object name.

-- Objects currently held under library cache locks
SELECT
  kgllkhdl                           AS handle_address,
  kgllkmod                           AS lock_mode,    -- 0=None 1=Null 2=Share 3=Exclusive
  kgllkreq                           AS lock_request,
  kgllkses                           AS session_address,
  kgllknam                           AS object_name
FROM x$kgllk
WHERE kgllkmod > 0
  AND kgllkreq > 0
ORDER BY kgllkmod DESC;

-- Simpler: library cache object stats showing high pin misses
SELECT
  namespace,
  gets,
  gethits,
  ROUND(gethitratio * 100, 2)  AS get_hit_pct,
  pins,
  pinhits,
  ROUND(pinhitratio * 100, 2)  AS pin_hit_pct,
  reloads,
  invalidations
FROM v$librarycache
ORDER BY reloads DESC;

Step 4 - Find the SQL Behind the Lock

-- SQL text for the blocking session's current and previous statement
SELECT
  sq.sql_id,
  sq.sql_text,
  sq.parsing_schema_name,
  sq.executions,
  sq.parse_calls,
  sq.loads,
  sq.invalidations,
  sq.first_load_time,
  sq.last_load_time
FROM v$sql sq
WHERE sq.sql_id IN (
  SELECT DISTINCT sql_id   FROM v$session
  WHERE sid IN (
    SELECT DISTINCT blocking_session FROM v$session
    WHERE event IN ('library cache lock','library cache pin','library cache load lock')
  )
  UNION
  SELECT DISTINCT prev_sql_id FROM v$session
  WHERE sid IN (
    SELECT DISTINCT blocking_session FROM v$session
    WHERE event IN ('library cache lock','library cache pin','library cache load lock')
  )
)
ORDER BY sq.loads DESC;

---

Common Causes

1. DDL Executed Against an Active Object

Issuing ALTER TABLE, CREATE OR REPLACE, DROP, TRUNCATE, or GRANT on an object that sessions are actively executing against forces Oracle to acquire an exclusive library cache lock. All sessions parsing or executing against that object queue behind the DDL.

-- Check for active DDL in flight
SELECT
  sid,
  serial#,
  username,
  sql_id,
  status,
  event,
  seconds_in_wait,
  command  -- 2=INSERT 3=SELECT 6=UPDATE 7=DELETE 9=CREATE 15=ALTER 47=PL/SQL
FROM v$session
WHERE command IN (9, 10, 12, 14, 15, 35)  -- DDL commands
  AND status = 'ACTIVE';

2. Package or Procedure Recompilation

Recompiling an invalidated package while sessions are using it causes library cache pin contention. This is common after schema changes, imports, or when dependencies are touched.

-- Find invalid objects that may be causing automatic recompilation
SELECT
  owner,
  object_name,
  object_type,
  status,
  last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
  AND object_type IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER','VIEW')
ORDER BY last_ddl_time DESC;

-- Manually compile all invalid objects (run in a maintenance window)
-- EXEC UTL_RECOMP.RECOMP_SERIAL();   -- serial
-- EXEC UTL_RECOMP.RECOMP_PARALLEL(4); -- parallel, 4 threads

3. Hard Parse Storms

Excessive hard parsing exhausts library cache latches and creates serialisation even without explicit DDL. Hard parses happen when cursor sharing is disabled and SQL uses literals instead of bind variables.

-- Identify high-parse-rate SQL (literals instead of bind variables)
SELECT
  force_matching_signature,
  COUNT(*)          AS cursor_count,
  MAX(sql_text)     AS sample_sql,
  SUM(parse_calls)  AS total_parses,
  SUM(executions)   AS total_execs
FROM v$sql
WHERE force_matching_signature != 0
GROUP BY force_matching_signature
HAVING COUNT(*) > 10
ORDER BY cursor_count DESC
FETCH FIRST 20 ROWS ONLY;

-- Overall parse ratio for the instance
SELECT
  ROUND(
    (SUM(CASE WHEN stat_name = 'hard parse elapsed time' THEN value END) /
     NULLIF(SUM(CASE WHEN stat_name = 'parse time elapsed' THEN value END), 0)) * 100, 2
  ) AS hard_parse_pct_of_parse_time
FROM v$sys_time_model
WHERE stat_name IN ('hard parse elapsed time', 'parse time elapsed');

4. Shared Pool Fragmentation

A fragmented shared pool forces frequent object aging out and reloading, increasing reload counts which translate to lock contention during reload.

-- Shared pool free memory and fragmentation
SELECT
  pool,
  name,
  ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name IN ('free memory', 'library cache', 'sql area', 'dictionary cache')
ORDER BY bytes DESC;

-- Reload rate - high values indicate pool pressure
SELECT
  namespace,
  reloads,
  invalidations,
  ROUND(reloads / NULLIF(gets, 0) * 100, 4) AS reload_pct
FROM v$librarycache
WHERE reloads > 0
ORDER BY reloads DESC;

---

Resolution Steps

Immediate Relief - Kill the Blocking Session

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.

-- Generate kill statement for the root blocker
SELECT
  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd,
  sid,
  serial#,
  username,
  event,
  seconds_in_wait,
  sql_id
FROM v$session
WHERE sid = (
  -- Find the root blocker (session not blocked by anything else)
  SELECT blocking_session
  FROM v$session
  WHERE event IN ('library cache lock','library cache pin')
    AND blocking_session IS NOT NULL
    AND blocking_session NOT IN (
          SELECT sid FROM v$session
          WHERE blocking_session IS NOT NULL
        )
  FETCH FIRST 1 ROWS ONLY
);

Fix 1 - Enable Cursor Sharing

If hard parse storms are the cause, enabling cursor sharing reduces the number of unique cursors loaded into the library cache.

-- Check current cursor_sharing setting
SHOW PARAMETER cursor_sharing;

-- Enable FORCE cursor sharing (session level for testing)
ALTER SESSION SET cursor_sharing = FORCE;

-- System-wide (requires restart or ALTER SYSTEM)
ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;

-- Verify effect - cursor count should reduce over time
SELECT COUNT(*) AS unique_cursors FROM v$sql;

Fix 2 - Increase Shared Pool

-- Current shared pool size
SHOW PARAMETER shared_pool_size;

-- Resize shared pool (if using ASMM/AMM this adjusts the target)
ALTER SYSTEM SET shared_pool_size = 2G SCOPE = BOTH;

-- If using AMM, increase memory_target instead
SHOW PARAMETER memory_target;
ALTER SYSTEM SET memory_target = 8G SCOPE = SPFILE;
-- Requires restart

Fix 3 - Pin Large Objects in the Library Cache

Pinning frequently used packages prevents them from being aged out and reloaded, eliminating the contention window during reload.

-- Pin a package in the shared pool
EXECUTE DBMS_SHARED_POOL.KEEP('SCHEMA_NAME.PACKAGE_NAME', 'P');

-- View currently pinned objects
SELECT * FROM v$db_object_cache WHERE kept = 'YES';

-- Unpin an object
EXECUTE DBMS_SHARED_POOL.UNKEEP('SCHEMA_NAME.PACKAGE_NAME', 'P');

Fix 4 - Flush the Shared Pool (Emergency Only)

-- Flush shared pool - causes immediate hard parse storm, use with caution
ALTER SYSTEM FLUSH SHARED_POOL;

---

Shared Pool Sizing Relationship

The shared pool must be large enough to hold all active SQL cursors, PL/SQL objects, and dictionary cache entries without constant aging. A pool that is too small drives up reload rates, which directly increases library cache lock contention.

-- Estimate shared pool size needed based on current workload
SELECT
  ROUND(SUM(sharable_mem) / 1024 / 1024, 2)  AS sql_area_mb,
  COUNT(*)                                     AS cursor_count,
  ROUND(AVG(sharable_mem) / 1024, 2)          AS avg_cursor_kb
FROM v$sql;

-- Check shared pool advisory
SELECT
  shared_pool_size_for_estimate / 1024 / 1024  AS pool_size_mb,
  estd_lc_size / 1024 / 1024                   AS est_lc_size_mb,
  estd_lc_time_saved_factor,
  estd_lc_memory_object_hits
FROM v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;

---

Cursor Sharing Impact

CURSOR_SHARING = FORCE replaces literals with system-generated bind variables, allowing multiple sessions executing the same SQL with different literal values to share a single cursor. This dramatically reduces library cache object count and contention.

-- Before/after comparison of cursor sharing effect
-- Count cursors per force_matching_signature (same SQL, different literals)
SELECT
  force_matching_signature,
  COUNT(*)     AS literal_variants,
  SUM(executions) AS total_executions,
  SUBSTR(MAX(sql_text), 1, 80) AS sample_text
FROM v$sql
WHERE force_matching_signature != 0
  AND parsing_schema_name NOT IN ('SYS','SYSTEM')
GROUP BY force_matching_signature
HAVING COUNT(*) > 5
ORDER BY literal_variants DESC
FETCH FIRST 20 ROWS ONLY;

---

Historical Analysis via ASH

-- Library cache waits in the last hour from ASH
SELECT
  TO_CHAR(sample_time, 'HH24:MI') AS sample_minute,
  event,
  COUNT(*)                          AS waiter_count,
  AVG(time_waited) / 1000           AS avg_wait_ms,
  MAX(time_waited) / 1000           AS max_wait_ms
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
  AND event IN (
        'library cache lock',
        'library cache pin',
        'library cache load lock',
        'cursor: pin S wait on X'
      )
GROUP BY TO_CHAR(sample_time, 'HH24:MI'), event
ORDER BY sample_minute, waiter_count DESC;

-- AWR historical view
SELECT
  TO_CHAR(sn.begin_interval_time, 'DD-MON HH24:MI') AS snap_time,
  ash.event,
  COUNT(*)                                            AS waiter_samples
FROM dba_hist_active_sess_history ash
JOIN dba_hist_snapshot sn
  ON ash.snap_id = sn.snap_id
WHERE sn.begin_interval_time > SYSDATE - 7
  AND ash.event IN ('library cache lock', 'library cache pin', 'library cache load lock')
GROUP BY TO_CHAR(sn.begin_interval_time, 'DD-MON HH24:MI'), ash.event
ORDER BY snap_time;

---

Best Practices

1. Use bind variables - The single most effective way to reduce library cache contention. Every unique literal creates a unique cursor.
2. Perform DDL during low-traffic windows - Never run CREATE OR REPLACE, ALTER, or GRANT during peak hours on active objects.
3. Compile invalid objects proactively - Run UTL_RECOMP during scheduled maintenance rather than letting Oracle auto-invalidate at runtime.
4. Size the shared pool generously - Aim for a reload rate below 1% in V$LIBRARYCACHE.
5. Pin large, frequently used packages - Use DBMS_SHARED_POOL.KEEP for packages called thousands of times per minute.
6. Monitor cursor: pin S wait on X - This mutex wait is the 12c+ equivalent of library cache pin and should be monitored alongside the classic events.

Related Topics

• Oracle AWR Overview - Historical performance analysis
• Active Session History (ASH) - Real-time session monitoring
• Performance Analysis Scripts - Additional wait event monitoring queries