Oracle library cache: mutex X and cursor: pin S wait on X - high version counts, hard parsing and hot objects

#Symptoms

• Sudden CPU spike; AWR/ASH top waits show library cache: mutex X, cursor: pin S wait on X, cursor: mutex X or cursor: mutex S.
• Hundreds of sessions run the same statement and pile up.
• The problem appears after an application release, a statistics gather, or at peak concurrency.

These waits mean many sessions compete for the same library cache structures: usually one SQL statement or object that is parsed, searched or pinned constantly.

#Step 1: which SQL and which mutex?

-- Last 15 minutes in ASH
SELECT event, sql_id, COUNT(*) AS samples
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 15/1440
AND    (event LIKE 'library cache%' OR event LIKE 'cursor:%')
GROUP  BY event, sql_id
ORDER  BY samples DESC
FETCH FIRST 15 ROWS ONLY;

Mutex sleep history shows the location and the object:

SELECT mutex_type, location, COUNT(*) AS sleeps, MAX(sleep_timestamp) AS last_seen
FROM   v$mutex_sleep_history
GROUP  BY mutex_type, location
ORDER  BY sleeps DESC
FETCH FIRST 10 ROWS ONLY;

#Step 2: high version count?

A statement with thousands of child cursors makes every execution search a long list under a mutex.

SELECT sql_id, version_count, executions, parse_calls, LEFT(sql_text, 80) AS sql_text
FROM   v$sqlarea
WHERE  version_count > 100
ORDER  BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

Why weren't existing children shared? V$SQL_SHARED_CURSOR has a Y column per reason:

SELECT *
FROM   v$sql_shared_cursor
WHERE  sql_id = '&sql_id'
FETCH FIRST 5 ROWS ONLY;

Easier: the REASON column (XML) summarises it, or count the Y flags across children with a short query or a script like Oracle's version_rpt (My Oracle Support).

#Step 3: hard parsing (no bind variables)

If version counts are low but library cache: mutex X is high with lots of different sql_ids doing the same thing with literals:

SELECT force_matching_signature, COUNT(*) AS distinct_sql, SUM(executions) AS execs,
       MAX(LEFT(sql_text, 80)) AS example
FROM   v$sql
WHERE  force_matching_signature > 0
GROUP  BY force_matching_signature
HAVING COUNT(*) > 50
ORDER  BY distinct_sql DESC
FETCH FIRST 10 ROWS ONLY;

Many SQL statements with the same force_matching_signature = the app builds SQL with literals.

• Proper fix: use bind variables in the application.
• Stop-gap: CURSOR_SHARING=FORCE at session level (logon trigger for that app's user) or system level. Test carefully: it can change plans and has its own side effects.

CREATE OR REPLACE TRIGGER app_cursor_sharing AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'LEGACY_APP' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing = FORCE';
  END IF;
END;
/

Check parse statistics:

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

Also check session_cached_cursors (default 50 in recent versions); apps that parse the same SQL repeatedly benefit from a larger value.

#Step 4: hot objects and hot cursors

Sometimes one very popular object (a package called millions of times, a small lookup table, a sequence) is the contention point even with good SQL.

• Mark hot: DBMS_SHARED_POOL.MARKHOT creates multiple copies of a hot library cache object to spread the mutex contention (documented; use on specific objects identified in mutex sleep history).

EXEC DBMS_SHARED_POOL.MARKHOT(schema => 'APP', objname => 'PKG_PRICING', namespace => 1);

• Sequences: use a large CACHE (and NOORDER in RAC) for high-insert sequences.
• Application connection storms: thousands of new sessions all parsing at once after an app restart; use connection pools with a minimum size and staged startup.

#Step 5: emergency relief

• Purge a single bad parent cursor to reset its children (temporary; they come back if the cause remains):

SELECT address, hash_value FROM v$sqlarea WHERE sql_id = '&sql_id';
EXEC DBMS_SHARED_POOL.PURGE('&address,&hash_value', 'C');

Caution

Don't flush the entire shared pool on a busy production system. Every session must then hard-parse at once, which often makes the mutex storm worse.

• If a recent application release introduced the pattern, rolling it back is often the fastest fix.

#Prevent it next time

• Monitor version_count and hard parse rate; alert on sudden increases.
• Review data access code for bind variable use and consistent bind types.
• Keep NLS and optimizer session settings consistent across app servers.
• Gather statistics outside peak times and avoid unnecessary invalidations.