Scops
Docs Download

Oracle problems

library cache lock, mutex X and cursor: pin S wait on X: The Fix

3 November 2026

Three waits with the words library cache or cursor in them, and one cause behind almost all of them: parsing. library cache lock is a session that wants an object another session holds, usually because that session is changing it, compiling it or granting on it. library cache: mutex X is a hash bucket of cursors held while a statement is loaded, which Oracle's reference says "is often associated with high parse activity". cursor: pin S wait on X is a session that wants to run a cursor another session is still building. None of them is fixed by memory alone. The queries below name the holder, the object and the parse rate, and the fix is a piece of DDL moved out of the day, a bind variable, or a cursor that stops being rebuilt.

What the waits mean

The reference describes library cache lock as the event that "controls the concurrency between clients of the library cache": a client acquires a lock on the object handle so that "one client can prevent other clients from accessing the same object", or so that "the client can maintain a dependency for a long time (for example, no other client can change the object)", and the lock "is also obtained to locate an object in the library cache". Its wait time is 3 seconds. For library cache: mutex X: "Library cache mutexes protect hash buckets within the shared pool. Each hash bucket contains a number of cursors. The mutex must be held in exclusive mode before any of the structures that it protects can be changed." And cursor: pin S wait on X: "A session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object." Its P2 carries the holder: the top two bytes are the SID of the session that holds the mutex in exclusive mode, which is the session doing the hard parse.

Who holds it, and on what

SELECT s.sid, s.event, s.blocking_session,
       CASE WHEN s.event = 'cursor: pin S wait on X'
            THEN TRUNC(s.p2 / 65536) END          AS mutex_holder,
       s.p1raw                                   AS handle_or_hash,
       ROUND(s.wait_time_micro / 1e6)            AS waited_s,
       s.sql_id, s.username, s.program
FROM   v$session s
WHERE  s.event IN ('library cache lock', 'library cache: mutex X',
                   'cursor: pin S wait on X')
ORDER  BY waited_s DESC;

For library cache lock, blocking_session names the holder, and the holder's own row in V$SESSION says what it is doing: an ALTER TABLE, a package compile, a GRANT, a statistics gather, or nothing at all, idle, with the lock held by a transaction it has not ended. For cursor: pin S wait on X the holder is mutex_holder, and its sql_id is the statement being parsed. The cursor itself, from its hash value:

SELECT owner, name, namespace, type, sharable_mem, loads,
       executions, locks, pins, invalidations
FROM   v$db_object_cache
WHERE  hash_value = &p1;

loads climbing against few executions is a cursor that is rebuilt over and over, and invalidations says why: a DDL or a statistics change on something it depends on throws every session back into a hard parse of the same statement at the same moment.

How much parsing there is

SELECT name, value
FROM   v$sysstat
WHERE  name IN ('parse count (total)', 'parse count (hard)',
                'parse count (failures)', 'parse time elapsed',
                'session cursor cache hits', 'execute count');

Two readings a minute apart give the rate. Hard parses close to total parses is literal SQL, no bind variables, which the shared pool page covers in full. Failures climbing is an application retrying a statement that errors on parse, which costs a hard parse every time and never shows in the slow-query lists. Then the cursors that exist in too many versions, each one a separate child that every new session has to search through under the mutex:

SELECT sql_id, version_count, loaded_versions, executions,
       invalidations, last_active_time
FROM   v$sqlarea
WHERE  version_count > 50
ORDER  BY version_count DESC FETCH FIRST 10 ROWS ONLY;

V$SQL_SHARED_CURSOR has one column for every reason a child could not be shared, and the columns that read Y for the top sql_id name the mismatch: a bind of a different length or type from each client, a different optimizer environment per session, a different language setting.

The fix, in order

  1. Move the DDL out of the day. A grant, a package compile, an index rebuild, a TRUNCATE or an ALTER TABLE on an object that a hundred sessions execute invalidates their cursors and takes the lock they all need. The same change at two in the morning takes the same lock with nobody behind it. A statistics gather with the default AUTO_INVALIDATE spreads the invalidations over time; one with NO_INVALIDATE => FALSE invalidates everything at once.
  2. End the holder that is not doing anything. A session that holds a library cache lock while idle is a transaction that did DDL, or a compile, and stopped; ALTER SYSTEM KILL SESSION releases it and the queue drains.
  3. Bind variables, or CURSOR_SHARING as the stopgap. Literal SQL hard-parses every execution and holds the mutex for each one. The application change is the fix; CURSOR_SHARING = FORCE buys the time to make it.
  4. Cut the version count. Fix the mismatch V$SQL_SHARED_CURSOR names: declare binds with one length and type from every client, keep the optimizer and session settings the same across the pool, and flush the one bloated cursor with DBMS_SHARED_POOL.PURGE while the fix ships.
  5. Cache the cursors. A higher SESSION_CACHED_CURSORS keeps a soft parse from walking the library cache at all, and an application that holds its prepared statements open instead of closing and re-preparing them stops asking in the first place.
  6. Fix the statement that fails to parse. parse count (failures) rising at a steady rate is one statement with a syntax error, a missing privilege or a dropped object that the application retries forever; the alert log and the application log name it.
  7. Then, and only then, memory. A shared pool that is too small ages cursors out and reloads them, which is a hard parse with a mutex for each reload; the ORA-04031 page has the sizing, and it comes after the parse rate, not before.

See it coming

Radar Engine, the detection layer of Scops, reads the parse counters and the shared pool every 5 minutes and the sessions every 5 seconds, so a parse storm is a finding with the statement and the rate while the first sessions queue on the mutex:

DetectorClockWhen it fires
Hard parse stormLive30 hard parses a second, far above the hour before, with shared pool problems: Warning. Five times the usual and 90 a second: Critical.
Shared pool drainingLive, every 5 minutesFree memory decreases: Warning. 3% or less: Critical.
Shared pool low on memoryHourly, on the worst instanceUnder 3%: Watch. Under 1% and well under its usual: Warning.
Concurrency or Application waits surgingLive, against the hour beforeTwice the hour before, well above its usual spread: Watch. With a second sign, such as 5 blocked sessions: Warning.
Sudden wait eventLiveA sudden large share of the waits: Watch. The largest one, if it continues: Warning.

The finding names the statement being parsed, the rate, and the session that holds the lock or the mutex with what it is running. The Activity chart draws the Concurrency class by statement and the parse counters next to it, on any edition and without the Diagnostics Pack.

Questions

What is library cache lock?
A session wants an object in the library cache that another session holds, usually because that session is changing it: DDL, a compile, a grant, or a statistics gather. V$SESSION's BLOCKING_SESSION names the holder, and the fix is to finish or end the holder and to move such changes out of the busy hours.

What is library cache: mutex X?
The mutex that protects a hash bucket of cursors in the shared pool, held in exclusive mode while a cursor is loaded or changed. Oracle's reference ties it to high parse activity: literal SQL, cursors with hundreds of versions, or a popular statement that was just invalidated.

What is cursor: pin S wait on X?
A session wants to execute a cursor that another session is still building, after a hard parse or an invalidation. The holder's SID is in the top two bytes of P2. A storm of these on one statement right after a DDL or a statistics change is the invalidation; the same storm all day is literal SQL.

How do I find who holds the library cache lock?
Query V$SESSION for the sessions on the event and read BLOCKING_SESSION; then look at the holder's own row for its status, its SQL_ID and how long it has been idle. A holder that is idle has a transaction open from a DDL or a compile and can be killed; an active one is a change in progress.

Related problems

Sources