library cache pin - Fix Oracle Package Compilation Waits

library cache pin

Overview

The library cache pin wait event fires when a session needs to access the heap (the compiled code or metadata body) of a library cache object and another session is holding an incompatible pin on that same heap. Where library cache locks protect the object handle, library cache pins protect the object’s actual content — the compiled representation of a PL/SQL package body, procedure, function, or trigger.

The most common scenario: a session executing a PL/SQL package acquires a shared pin (mode S) on that package’s heap. If another session simultaneously attempts to recompile that package — which requires an exclusive pin (mode X) — the recompiling session must wait until all shared pinners complete their current call. Meanwhile, any further session attempting to execute the same package also waits behind the pending exclusive pin request.

This wait can also occur with:

• Triggers: Recompiling a trigger while DML is active on the table fires this event
• Views: Recompiling a view while queries are in mid-parse fires this event
• Types: Altering a type while objects of that type are in use

Library cache pin is closely related to library cache lock, but they target different internal structures. In diagnostic output, you will often see both events together when a DDL/recompilation is running against an active object — the DDL must first acquire the lock, then the pin.

In Oracle 11g and later, many latch-based library cache operations were migrated to mutex-based protection. As a result, on 11g+ systems you may see cursor: pin S wait on X more frequently than classic library cache pin. However, library cache pin remains the dominant event for object-level compilation waits.

When This Wait Is a Problem

Acceptable: Single, brief library cache pin waits (under 2 seconds) during a deliberate recompilation are normal. Sessions stall momentarily then continue.

Investigate when:

• Multiple sessions accumulate with library cache pin waits exceeding 10 seconds
• Production application timeouts coincide with deployment or DBMS_UTILITY.COMPILE_SCHEMA execution
• The event appears consistently in AWR top waits across multiple report periods
• ORA-04021 (timeout while waiting to lock object) errors appear in application logs

Critical scenario: A junior DBA runs ALTER PACKAGE BODY payment_pkg COMPILE against a heavily-used payment processing package during business hours. Every transaction that calls that package will freeze until the recompile finishes — which could take seconds or minutes depending on package size.

The P1 parameter encodes the library cache handle address, P2 encodes the pin mode, and P3 encodes the pin duration. With these values, you can cross-reference X$KGLPN to find the exact package being pinned and the holder.

Diagnostic Queries

1. Identify Waiting Sessions and the Object Being Compiled

-- Sessions waiting on library cache pin
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.module,
    s.action,
    s.event,
    s.seconds_in_wait,
    s.p1raw AS handle_address,
    s.p2 AS pin_mode_requested,
    s.p3 AS pin_duration,
    s.sql_id,
    s.status
FROM
    v$session s
WHERE
    s.event = 'library cache pin'
ORDER BY
    s.seconds_in_wait DESC;

2. Find the Holder via X$KGLPN (Kernel Generic Library cache PiN)

-- Find the session holding the exclusive pin causing the wait
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.event AS current_event,
    s.sql_id,
    kglpn.kglpnmod AS pin_mode_held,
    kglpn.kglpnreq AS pin_mode_requested,
    kglob.kglobtyd AS object_type,
    kglob.kglnaown AS object_owner,
    kglob.kglnaobj AS object_name,
    kglob.kglhdldc AS dependency_count
FROM
    x$kglpn kglpn
    JOIN v$session s
        ON kglpn.kglpnses = s.saddr
    JOIN x$kglob kglob
        ON kglpn.kglpnhdl = kglob.kglhdadr
WHERE
    kglpn.kglpnmod != 0  -- Actively holding a pin
ORDER BY
    kglpn.kglpnmod DESC,
    s.sid;

3. V$SYSTEM_EVENT — Quantify Historical Pin Waits

-- Quantify library cache pin and related events
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2) AS total_wait_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 pin',
        'library cache lock',
        'cursor: pin S wait on X',
        'library cache: mutex X'
    )
ORDER BY
    total_wait_secs DESC;

4. ASH Analysis — When Did Pin Contention Occur?

-- Active Session History for library cache pin events
-- Identify the objects and SQL involved over the last 24 hours
SELECT
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') AS sample_time,
    ash.session_id,
    ash.blocking_session,
    ash.sql_id,
    ash.current_obj#,
    o.object_name,
    o.object_type,
    o.owner,
    ash.program,
    ash.module,
    ash.action
FROM
    v$active_session_history ash
    LEFT JOIN dba_objects o
        ON ash.current_obj# = o.object_id
WHERE
    ash.event = 'library cache pin'
    AND ash.sample_time > SYSDATE - 1
ORDER BY
    ash.sample_time DESC
FETCH FIRST 100 ROWS ONLY;

5. Identify Packages with High Execution Frequency (Pin Contention Risk)

-- High-execution packages that would be impacted most by recompilation
SELECT
    o.owner,
    o.object_name,
    o.object_type,
    o.status,
    o.last_ddl_time,
    NVL(s.executions, 0) AS executions,
    NVL(s.loads, 0) AS loads,
    NVL(s.invalidations, 0) AS invalidations
FROM
    dba_objects o
    LEFT JOIN v$db_object_cache s
        ON o.object_name = s.name
        AND o.owner = s.owner
        AND o.object_type = s.type
WHERE
    o.object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
    AND o.status = 'VALID'
ORDER BY
    executions DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY;

6. Check for Objects Currently Being Compiled

-- Sessions currently executing DDL that would hold exclusive pins
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.sql_id,
    q.sql_text,
    s.seconds_in_wait,
    s.event
FROM
    v$session s
    LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
    s.status = 'ACTIVE'
    AND (
        UPPER(q.sql_text) LIKE '%ALTER%COMPILE%'
        OR UPPER(q.sql_text) LIKE '%CREATE OR REPLACE%'
        OR s.event IN ('library cache pin', 'library cache lock')
    )
ORDER BY
    s.seconds_in_wait DESC;

Root Causes

1. Package Body Recompilation During Active Execution

The most common cause by far. A developer or automated deployment script executes ALTER PACKAGE BODY pkg_name COMPILE while production sessions are actively calling procedures within that package. The recompiler acquires an exclusive pin, and every new caller must wait for the recompilation to complete. In large packages with complex body initialization, recompilation may take 5–30 seconds.

2. DBMS_UTILITY.COMPILE_SCHEMA Executed During Business Hours

DBMS_UTILITY.COMPILE_SCHEMA recompiles all INVALID objects in a schema in an uncontrolled order. When run during peak hours, it acquires exclusive pins on one object after another. Each recompilation momentarily blocks all sessions using that object. If many objects compile concurrently (e.g., after a large table alteration), dozens of sessions can pile up.

3. Trigger Recompilation During DML

A trigger is automatically invalidated when its table undergoes structural DDL (ADD COLUMN, MODIFY COLUMN, etc.). The next DML statement against that table triggers automatic recompilation of the trigger. If many sessions simultaneously attempt their first DML post-DDL, they contend for the exclusive pin to perform the recompilation.

4. Dependency Chain Recompilation

Altering a type or package specification invalidates all objects that depend on it. When those dependents are next executed, Oracle must recompile them all. A deeply nested dependency chain (type → package → procedure → trigger) can create sequential pin acquisition, stalling many session types simultaneously.

5. Long-Running Compilations (Large Packages)

Some PL/SQL package bodies are thousands of lines long with complex initialization blocks. A CREATE OR REPLACE PACKAGE BODY on such a package may hold an exclusive pin for 30 seconds or more. Any session needing that package during this period will wait for the full compilation duration.

Resolution Steps

Step 1: Identify and Kill the Blocking Compilation Session

-- Kill the session holding the exclusive pin
-- First confirm it is the recompilation session (not a legitimate user)
-- Use X$KGLPN query above to identify sid/serial#
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Step 2: Use UTL_RECOMP for Controlled Post-Deployment Recompilation

-- After deploying DDL changes, recompile in dependency order
-- Serial mode: Slower but generates minimal concurrent pin contention
EXEC UTL_RECOMP.RECOMP_SERIAL();

-- Serial mode for a specific schema only
EXEC UTL_RECOMP.RECOMP_SERIAL('HR');

-- Parallel mode with controlled degree (use during maintenance only)
EXEC UTL_RECOMP.RECOMP_PARALLEL(
    threads => 4,
    schema  => 'HR'
);

Step 3: Pre-Warm Recompiled Objects After Deployment

After recompilation completes, pin frequently-used packages in the shared pool to prevent cold-cache parse delays:

-- Pin critical packages in the shared pool to prevent aging out
-- This also ensures they are compiled and ready before application load
EXEC DBMS_SHARED_POOL.KEEP('HR.PAYMENT_PKG', 'P');
EXEC DBMS_SHARED_POOL.KEEP('HR.ORDER_PKG', 'P');

-- Verify pinned objects
SELECT owner, name, type, kept
FROM v$db_object_cache
WHERE kept = 'YES';

Step 4: Implement Edition-Based Redefinition (EBR) for Zero-Downtime Deployments

-- EBR allows new package versions to be deployed in a new edition
-- Old sessions use the old edition; new sessions use the new edition
-- No library cache pin contention between old and new code paths

-- Enable editions for a schema
ALTER USER hr ENABLE EDITIONS;

-- Create a new edition
CREATE EDITION v2 AS CHILD OF ora$base;

-- Deploy new code in the new edition
ALTER SESSION SET EDITION = v2;
CREATE OR REPLACE PACKAGE BODY hr.payment_pkg AS
    -- New implementation
END;
/

-- Switch sessions to new edition via connection pool configuration
-- Old sessions continue using ora$base until they reconnect

Step 5: Avoid Recompilation During Peak Hours

Enforce a deployment policy at the process level. Use a guard procedure:

-- Example pre-deployment check
DECLARE
    v_active_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_active_count
    FROM v$session
    WHERE status = 'ACTIVE'
      AND username NOT IN ('SYS', 'SYSTEM')
      AND program NOT LIKE '%JDBC%'  -- Adjust for your connection pool
      AND logon_time > SYSDATE - 1/24;

    IF v_active_count > 10 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Too many active sessions (' || v_active_count ||
            ') for safe deployment. Wait for maintenance window.'
        );
    END IF;
END;
/

Prevention & Tuning

Deploy using Edition-Based Redefinition for packages and procedures that are heavily used in production. EBR is the only Oracle-native mechanism that truly eliminates library cache pin contention during code deployment.

Schedule DBMS_UTILITY.COMPILE_SCHEMA and UTL_RECOMP.RECOMP_PARALLEL exclusively during maintenance windows: These procedures hammer the library cache with exclusive pins. Never run them during business hours.

Keep package bodies small and cohesive: Large monolithic package bodies take longer to compile, extending the duration of exclusive pin hold. Splitting large packages into smaller, functionally focused units reduces compilation time and pin contention duration.

Monitor INVALID object counts continuously:

-- Alert if invalid objects exceed threshold
SELECT owner, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
  AND object_type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
GROUP BY owner
HAVING COUNT(*) > 5
ORDER BY invalid_count DESC;

Use DBMS_SHARED_POOL.KEEP for critical packages: Pinning frequently-called packages prevents them from aging out of the shared pool, which would require a full reparse/recompile on the next call — temporarily holding an exclusive pin.

Implement rolling deployments with connection draining: Before deploying package changes, drain active connections from the target application tier, deploy, recompile, then restore connections.

Related Wait Events

• library cache lock — Lock on the object handle (precedes pin acquisition during DDL)
• cursor: pin S wait on X — Session-private cursor pin contention; often co-occurs in high-concurrency parse scenarios
• library cache: mutex X — Mutex-based library cache protection in 11g+; functionally similar to library cache pin for cursor heaps
• latch: library cache — Latch protecting library cache hash chain; fires during extremely high parse rates
• latch: shared pool — Shared pool memory allocation latch; may co-occur when large packages are being compiled