library cache lock - Diagnose Oracle DDL & Parse Contention

library cache lock

Overview

The library cache lock wait event occurs when a session needs to acquire a lock on a library cache object — a stored object such as a table, view, synonym, procedure, package, trigger, or type — and another session is already holding an incompatible lock on that object.

The Oracle library cache is the portion of the shared pool that stores parsed SQL statements, PL/SQL compilation units, and metadata about schema objects. To prevent corruption during concurrent access, Oracle uses two locking mechanisms on library cache objects: library cache locks (also called handles) and library cache pins. Locks control access to the object handle itself; pins control access to the object’s heap (the actual compiled code or metadata).

Library cache lock specifically fires when:

• A DDL statement (ALTER TABLE, CREATE OR REPLACE PROCEDURE, GRANT, REVOKE, DROP) needs an exclusive lock on an object while DML sessions hold shared locks
• An invalidated object needs to be revalidated/recompiled and another session holds a conflicting lock
• A session executing a SQL statement needs a shared lock on a referenced object while a DDL is in flight

This wait is distinct from library cache pin (which fires during PL/SQL compilation) and from cursor: pin S wait on X (which fires on individual cursor pins). Library cache lock waits almost always point to DDL being executed against objects that are actively in use.

When This Wait Is a Problem

Acceptable: Brief library cache lock waits (under 1 second) during planned schema changes are normal — sessions briefly stall while DDL completes.

Investigate when:

• Sessions accumulate waiting for library cache lock for more than 5–10 seconds
• The wait appears repeatedly in AWR top events across multiple snapshots
• Application timeouts or ORA-04021 errors begin occurring
• A schema migration or deployment is running while the application is live

Critical: If dozens of sessions pile up on library cache lock, it can cascade into a library cache latch contention event, effectively halting all parse activity for the database.

The P1 parameter is the library cache handle address, P2 is the lock mode requested, and P3 is the lock mode held by the blocker. These values combined with X$KGLLK enable precise identification of the blocking session and the object being contended.

Diagnostic Queries

1. Find Sessions Currently Waiting for Library Cache Locks

-- Active library cache lock waiters and their blockers
SELECT
    w.sid AS waiting_sid,
    w.serial# AS waiting_serial,
    w.username AS waiting_user,
    w.program AS waiting_program,
    w.event,
    w.seconds_in_wait,
    w.p1raw AS handle_address,
    w.p2 AS lock_mode_requested,
    w.p3 AS lock_mode_held_by_blocker,
    w.sql_id AS waiting_sql_id
FROM
    v$session w
WHERE
    w.event = 'library cache lock'
    AND w.wait_class != 'Idle'
ORDER BY
    w.seconds_in_wait DESC;

2. Identify the Blocking Session via X$KGLLK

-- Find who holds the library cache lock that others are waiting for
-- X$KGLLK: Kernel Generic Library cache Lock
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.sql_id,
    s.event,
    kgllk.kgllkmod AS lock_mode_held,
    kgllk.kgllkreq AS lock_mode_requested,
    kgllknam.kglobtyd AS object_type,
    kgllknam.kglnaown AS object_owner,
    kgllknam.kglnaobj AS object_name
FROM
    x$kgllk kgllk
    JOIN v$session s
        ON kgllk.kgllkses = s.saddr
    JOIN x$kglob kgllknam
        ON kgllk.kgllkhdl = kgllknam.kglhdadr
WHERE
    kgllk.kgllkmod != 0  -- Mode != null (actually holds a lock)
    AND kgllknam.kglobtyd NOT IN ('CURSOR', 'INVALID OBJECT')
ORDER BY
    kgllk.kgllkmod DESC,
    s.sid;

3. V$SYSTEM_EVENT — Quantify the Wait

-- Historical wait totals for library cache events
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2) AS time_waited_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(max_wait / 100, 2) AS max_wait_secs
FROM
    v$system_event
WHERE
    event IN (
        'library cache lock',
        'library cache pin',
        'library cache: mutex X',
        'cursor: pin S wait on X',
        'latch: library cache'
    )
ORDER BY
    time_waited DESC;

4. ASH — Historical Library Cache Lock Patterns

-- Historical library cache lock events from Active Session History
SELECT
    ash.sample_time,
    ash.sql_id,
    ash.session_id,
    ash.blocking_session,
    ash.event,
    ash.current_obj#,
    o.object_name,
    o.object_type,
    o.owner,
    ash.program,
    ash.module
FROM
    v$active_session_history ash
    LEFT JOIN dba_objects o
        ON ash.current_obj# = o.object_id
WHERE
    ash.event = 'library cache lock'
    AND ash.sample_time > SYSDATE - 1  -- Last 24 hours
ORDER BY
    ash.sample_time DESC
FETCH FIRST 50 ROWS ONLY;

5. Find Invalid Objects That May Trigger Recompilation Cascades

-- Invalid objects that cause lock chains during recompilation
SELECT
    owner,
    object_name,
    object_type,
    status,
    last_ddl_time
FROM
    dba_objects
WHERE
    status = 'INVALID'
    AND object_type IN (
        'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY',
        'TRIGGER', 'VIEW', 'TYPE', 'TYPE BODY'
    )
ORDER BY
    owner, object_type, object_name;

6. Dependency Chain Analysis

-- Find objects that depend on a specific object
-- Use when a DDL against one object causes a cascade of invalidations
SELECT
    d.owner AS dependent_owner,
    d.name AS dependent_name,
    d.type AS dependent_type,
    d.referenced_owner,
    d.referenced_name,
    d.referenced_type
FROM
    dba_dependencies d
WHERE
    d.referenced_name = 'YOUR_TABLE_OR_PACKAGE_NAME'  -- Replace with target object
    AND d.referenced_owner = 'YOUR_SCHEMA'
ORDER BY
    d.owner, d.type, d.name;

Root Causes

1. DDL Executed Against In-Use Objects

The most common cause. When a developer or DBA runs ALTER TABLE, CREATE OR REPLACE PROCEDURE, GRANT, REVOKE, or DROP against an object that active DML sessions are referencing, Oracle must acquire an exclusive (X) library cache lock on the object. Active DML sessions hold shared (S) library cache locks — the exclusive request blocks until all shared holders release their locks. Conversely, the DML sessions that arrive after the DDL began will wait for the exclusive DDL lock to be released.

This creates a two-sided blockade: the DDL waits for existing DML to finish, and new DML waits for the DDL to complete. In a busy OLTP system, the queue can grow rapidly.

2. Concurrent ALTER/GRANT Statements on the Same Object

Multiple DBA sessions executing DDL against the same object simultaneously contend with each other as well as with production DML. GRANT and REVOKE statements also acquire library cache locks because they invalidate cached cursor metadata for the affected object.

3. Dependency Invalidation Cascades

When a base object (table, package, type) is altered, Oracle marks all dependent objects as INVALID. When those dependents are next executed, Oracle must recompile them — and recompilation requires exclusive library cache locks. If a heavily-depended-upon package is invalidated, dozens of objects may attempt simultaneous recompilation, generating a storm of library cache lock waits.

4. Schema Comparison Tools and Third-Party Deployment Scripts

Many schema management tools (Flyway, Liquibase, custom deployment scripts) execute DDL without awareness of active database sessions. Executing a migration script during peak hours without draining connections first is a reliable way to generate library cache lock pile-ups.

5. Long-Running Parsing Sessions

A session that is mid-parse on a complex SQL statement holds shared library cache locks on all referenced objects during the parse phase. If this parse takes a long time (due to complex query transformation or optimizer statistics gathering), it delays any DDL that needs an exclusive lock on those objects.

Resolution Steps

Step 1: Identify and Kill the Root Blocker

-- Find the session holding the lock that others wait on
-- Use the X$KGLLK query above to identify the blocker
-- Then kill it if appropriate:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- In RAC: include the instance ID
ALTER SYSTEM KILL SESSION 'sid,serial#,@instance_id' IMMEDIATE;

Step 2: Drain Active Sessions Before DDL in Production

-- Best practice: Check for active sessions on the target object before DDL
-- Check for sessions referencing the object
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.sql_id,
    q.sql_text
FROM
    v$session s
    JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
    UPPER(q.sql_text) LIKE '%YOUR_OBJECT_NAME%'
    AND s.status = 'ACTIVE';

Step 3: Fix Invalid Objects After DDL

-- Recompile all invalid objects after a schema change
-- Run UTL_RECOMP for a controlled, ordered recompilation
EXEC UTL_RECOMP.RECOMP_SERIAL();  -- Serial (safer, slower)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);  -- Parallel (faster, more lock activity)

-- Or recompile a specific schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'YOUR_SCHEMA', compile_all => FALSE);

Step 4: Use ONLINE DDL Operations Where Available

-- Oracle 12c+: Online table redefinition avoids exclusive locks
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
    uname      => 'HR',
    orig_table  => 'EMPLOYEES',
    int_table   => 'EMPLOYEES_NEW'
);

-- Oracle 23ai: Certain ALTER TABLE operations are lockless
ALTER TABLE employees ADD (department_code VARCHAR2(10));
-- Many ADD COLUMN operations in 23ai don't require table-level locks

Step 5: Schedule DDL for Maintenance Windows

Implement a deployment policy that enforces schema changes only during low-traffic windows. Use Oracle Scheduler to automate migrations:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'SCHEMA_MIGRATION_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN execute_migration_v42; END;',
        start_date      => SYSTIMESTAMP AT TIME ZONE 'America/New_York',
        repeat_interval => NULL,
        enabled         => TRUE,
        comments        => 'One-time schema migration during maintenance window'
    );
END;
/

Prevention & Tuning

Never execute DDL during peak hours without a maintenance window: Establish and enforce a deployment policy that requires schema changes to be applied during agreed maintenance windows with active session monitoring.

Check for active users before DDL: Build pre-check queries into deployment scripts that abort if active sessions reference the target objects.

Use Edition-Based Redefinition (EBR) for zero-downtime deployments: EBR allows new code to be deployed in a new edition while old sessions continue using the previous edition — eliminating library cache lock contention during deployment entirely.

Minimize dependency chain depth: Deep chains of PL/SQL dependencies (package A calls package B calls package C) mean that altering a low-level object invalidates many dependent objects simultaneously. Design PL/SQL packages to minimize dependency depth.

Monitor for invalid objects proactively: Schedule a nightly job to alert on INVALID objects so they can be recompiled before they cause runtime recompilation storms during peak load.

-- Proactive check: count invalid objects by schema
SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;

Related Wait Events

• library cache pin — Contention during PL/SQL compilation (pin on the heap, not the handle)
• cursor: pin S wait on X — Session-private cursor pin contention, often from high-parse-rate applications
• latch: library cache — Latch protecting library cache bucket chains; indicates very high parse rates
• latch: shared pool — Shared pool memory allocation contention, often co-occurs during invalidation storms
• library cache: mutex X — Mutex-based equivalent of library cache latch in newer Oracle versions