When library cache: mutex X shows up as a dominant wait event, the database is burning CPU on parsing rather than on query execution. Sessions serialize behind exclusive mutexes that protect the shared SQL area, throughput erodes, and the instance still reports OPEN/ACTIVE on every basic availability check. The shared SQL cache cannot keep up with the rate of new SQL text the application is sending.

This pattern almost always appears alongside elevated parse count (hard), declining shared pool free memory, and the sibling waits cursor: pin S wait on X and latch: shared pool. If the rate is high enough and sustained, shared pool fragmentation follows and ORA-04031 becomes a real risk.

What this means

library cache: mutex X is a concurrency wait on the shared pool, not a storage or locking problem. When a session wants to add, invalidate, or modify a library cache object (a cursor, its plan, or its metadata), it must acquire a mutex protecting the relevant hash bucket in exclusive mode. Other sessions that need to look up, pin, or invalidate cursors mapped to the same bucket wait until the holder releases.

The wait becomes a problem when the rate of exclusive operations is high enough that the queue of waiters grows faster than it drains. Two patterns dominate:

  1. High hard parse rate. Every unique SQL text requires a hard parse, which acquires exclusive mutexes to compile, store, and optimize the new cursor. Literal SQL (no bind variables) at high call rates saturates the bucket mutexes even when individual parses are fast.
  2. High child-cursor version counts. When one parent cursor accumulates many child cursors (adaptive cursor sharing, bind peeking, mismatched session environments, statistics churn), every soft parse walks a longer chain, holding mutexes longer per lookup and increasing collision probability.

cursor: pin S wait on X is the closely related sibling: a session wants to pin an existing cursor in shared mode but another session is holding it in exclusive mode for loading or invalidation. latch: shared pool appears when contention spreads to shared pool memory allocation. Seeing all three together is a strong signal that the root cause is shared SQL area pressure.

A healthy OLTP system keeps hard parses well below 1% of total parses. When the ratio crosses 5%, library cache mutex contention becomes structurally likely at scale.

Common causes

CauseWhat it looks likeFirst thing to check
Literal SQL (no bind variables)V$SQL shows many near-identical statements differing only in literal values; each has EXECUTIONS = 1SELECT SQL_TEXT FROM V$SQL WHERE EXECUTIONS = 1 ORDER BY LAST_ACTIVE_TIME DESC
High child-cursor version countFew parent SQL_IDs with many child cursors; cursor: pin S wait on X correlated with mutex XV$SQL_SHARED_CURSOR for the offending SQL_ID
Shared pool flushSudden spike in hard parses after a manual ALTER SYSTEM FLUSH SHARED_POOLAlert log or DBA activity around onset time
Shared pool undersizedFree memory in shared pool under 5%, frequent ASMM resize oscillationV$SGASTAT for shared pool free memory
Adaptive Cursor Sharing churnMany BIND_EQUIV_FAILURE reasons in V$SQL_SHARED_CURSOR, often after upgradeCheck current RU for known ACS or Cardinality Feedback bugs
Cursor invalidations from DDL/statsV$SQL.INVALIDATIONS climbing on hot SQLDBMS_STATS schedule and DDL audit trail

Quick checks

All queries below are read-only and safe during an incident.

-- Confirm hard parse rate. Sample twice, 60 seconds apart.
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE 'parse count%';
-- Hard parse ratio target: below 1% of total. Above 5% is a structural problem.

-- Library cache mutex wait profile.
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO/NULLIF(TOTAL_WAITS,0)/1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT IN (
  'library cache: mutex X',
  'cursor: pin S wait on X',
  'cursor: mutex S',
  'cursor: mutex X',
  'latch: shared pool'
)
ORDER BY TIME_WAITED_MICRO DESC;

-- Current waiters, right now.
SELECT SID, SERIAL#, EVENT, SECONDS_IN_WAIT, SQL_ID, BLOCKING_SESSION
FROM V$SESSION
WHERE EVENT IN ('library cache: mutex X', 'cursor: pin S wait on X')
  AND STATE = 'WAITING'
ORDER BY SECONDS_IN_WAIT DESC;

-- Shared pool free memory. Below 5% of pool is a red flag.
SELECT POOL, NAME, BYTES/1048576 AS mb
FROM V$SGASTAT
WHERE POOL = 'shared pool' AND NAME = 'free memory';

-- Look for one-shot literal statements.
-- FETCH FIRST requires 12c+. Use ROWNUM <= 20 on 11g.
SELECT SQL_ID, EXECUTIONS, ELAPSED_TIME, BUFFER_GETS, SUBSTR(SQL_TEXT,1,80) AS sql_text
FROM V$SQL
WHERE EXECUTIONS = 1
ORDER BY LAST_ACTIVE_TIME DESC
FETCH FIRST 20 ROWS ONLY;

-- Parent cursors with many children (version-count problem).
SELECT SQL_ID, COUNT(*) AS child_count
FROM V$SQL
GROUP BY SQL_ID
HAVING COUNT(*) > 10
ORDER BY child_count DESC;

V$ACTIVE_SESSION_HISTORY and AWR are Diagnostics Pack features (Enterprise Edition only). Do not query them without that license.

How to diagnose it

  1. Confirm the wait is actually library cache mutex X. Start with the wait profile query above. If library cache: mutex X and its sibling events account for more than 5% of non-idle DB time, the diagnosis fits. If they are below noise, look elsewhere: CPU saturation, enq: TX, or log file sync.
  2. Quantify the hard parse rate. Sample parse count (total) and parse count (hard) twice, 60 seconds apart. Compute hard / total * 100. Above 5% is structural; above 20% is severe. Compare against CPU utilization: high CPU with high hard parse rate and library cache mutex waits is the textbook Parse Storm signature.
  3. Distinguish literal SQL from version-count explosion.
    • If V$SQL shows many unique SQL_TEXTs with EXECUTIONS = 1, the root cause is literal SQL. The application is sending new text for every call.
    • If a small number of SQL_IDs each have dozens or hundreds of child cursors, the root cause is version count. The parent cursor is being shared, but every session generates a new child.
  4. For version-count problems, identify the non-sharing reason. V$SQL_SHARED_CURSOR exposes why Oracle refused to reuse an existing child. Common reasons: BIND_EQUIV_FAILURE (adaptive cursor sharing), OPTIMIZER_MISMATCH, LANGUAGE_MISMATCH, TRANSLATION_MISMATCH. Each points to a different fix.
  5. Check for recent changes. Was CURSOR_SHARING modified? Was the shared pool flushed? Did a new build deploy? Did DBMS_STATS run? Did an upgrade land recently? Some 19c RUs have introduced BIND_EQUIV_FAILURE churn from Adaptive Cursor Sharing and Cardinality Feedback.
  6. Correlate with shared pool health. Falling free memory plus rising mutex waits plus eventual ORA-04031 is the cascade. Catch it before ORA-04031, because once it starts, fix attempts become harder.
flowchart TD
    A[library cache mutex X dominant] --> B{Hard parse ratio above 5%}
    B -- Yes --> C[Literal SQL pattern]
    B -- No --> D{High child cursor counts}
    C --> E[V$SQL EXECUTIONS = 1, near-identical text]
    D -- Yes --> F[V$SQL_SHARED_CURSOR reasons]
    D -- No --> G[Check invalidations, shared pool size]
    E --> H[CURSOR_SHARING FORCE short term, binds long term]
    F --> I[Fix session env, ACS, stats churn]
    G --> J[Address invalidation source, resize shared pool]

Metrics and signals to monitor

SignalWhy it mattersWarning sign
parse count (hard) / parse count (total) ratioDirect measure of how often the optimizer is invoked from scratchRatio above 1% sustained, above 5% severe
Hard parses per secondAbsolute parse pressureAbove 100/sec is common contention territory for OLTP
library cache: mutex X, cursor: pin S wait on X wait timeThe wait events themselvesAbove 5% of non-idle DB time
latch: shared pool wait timeShared pool memory allocation contentionAny sustained presence
Shared pool free memoryHeadroom before ORA-04031Below 5% of pool size
V$SQL.INVALIDATIONSCursor invalidation rate, a parse storm triggerClimbing on hot SQL_IDs
Child cursor count per parentVersion-count pressureAbove 10 children per parent for hot SQL
V$SGA_RESIZE_OPS oscillationASMM robbing buffer cache to feed shared poolFrequent back-and-forth resizes
CPU utilizationHard parsing is CPU-intensiveHigh CPU plus high hard parse rate
ORA-04031 in alert logShared pool OOM has begunAny occurrence

Fixes

Literal SQL (the most common cause)

Short term, while the application is being fixed:

-- Side effects possible. Test before applying in production.
ALTER SYSTEM SET CURSOR_SHARING = FORCE;

CURSOR_SHARING = FORCE rewrites literals in incoming SQL into system-generated bind variables, collapsing many unique texts into one. It can change plan quality for the worse in edge cases (bind peeking) and is not a permanent fix. The application should be modified to use real bind variables. Do not use CURSOR_SHARING = SIMILAR: it has been deprecated since 11.2.

High child-cursor version counts

Read V$SQL_SHARED_CURSOR for the offending SQL_ID and act on the most common reason:

  • BIND_EQUIV_FAILURE: adaptive cursor sharing is generating a new child for every bind set. Investigate whether ACS is needed at all for this SQL; in some 19c RUs the right move is a one-off _fix_control after confirming the bug with Oracle Support.
  • OPTIMIZER_MISMATCH: sessions are using different optimizer parameters or features. Standardize the session environment via logon trigger or application connection settings.
  • LANGUAGE_MISMATCH / TRANSLATION_MISMATCH: clients are using different NLS settings. Standardize NLS at the connection pool.
  • Statistics-driven churn: review whether DBMS_STATS is running too aggressively or producing unstable histograms.

For a small set of extremely hot cursors, DBMS_SHARED_POOL.MARKHOT is an option in 11.2.0.3+. It tells Oracle to maintain multiple copies of the cursor to spread mutex load. It is a targeted intervention, not a blanket fix. It has documented gotchas: you must use the correct namespace (namespace => 0 for SQLAREA cursors) and the hash value from X$KGLOB, not the SQL_ID. Applying it during partition exchange operations can make things worse. Confirm with Oracle Support for your specific SQL pattern and version.

Shared pool pressure (sizing and ASMM)

If free memory is consistently under 5% and ASMM is oscillating between buffer cache and shared pool, increase SGA_TARGET or raise the shared pool minimum. Do not flush the shared pool as a routine remedy; ALTER SYSTEM FLUSH SHARED_POOL triggers a hard parse storm and is almost never the right action.

Recent-upgrade regressions

If mutex contention appeared shortly after a database upgrade, check whether Adaptive Cursor Sharing or Cardinality Feedback is generating excessive child cursors. Known bugs in this category have been documented across 12c to 19c upgrades; the right fix is the patch or _fix_control recommended by Oracle Support for your RU, not disabling adaptive features system-wide without analysis.

Prevention

  • Bind variables everywhere. Make this a code-review gate. ORMs that emit literal SQL by default are the single largest preventable source of library cache mutex contention.
  • Trend the hard parse ratio. Alert on ratio above 1% sustained. The cost of catching this early is trivial; the cost of catching it during an ORA-04031 cascade is not.
  • Track child cursor counts for top SQL. A parent cursor that suddenly grows from 4 children to 200 is the earliest signal of ACS or environment-mismatch problems.
  • Monitor shared pool free memory and SGA resize oscillation as leading indicators of ORA-04031.
  • Treat CURSOR_SHARING = FORCE as a workaround, not a strategy. It has plan-quality side effects and masks the underlying application issue.
  • Never flush the shared pool on a schedule. The hard parse storm it causes is worse than the fragmentation it claims to fix.
  • Standardize session NLS and optimizer environments in connection pool configuration to prevent version-count explosions.

How Netdata helps

  • Per-second hard parse rate and parse ratio derived from V$SYSSTAT, so the parse storm is visible before it becomes a saturation event.
  • Library cache mutex wait time series (library cache: mutex X, cursor: pin S wait on X, cursor: mutex S, cursor: mutex X, latch: shared pool) on the same timeline as CPU and shared pool free memory, so the correlation is visible without manual ASH digging.
  • Alerting on hard parse ratio above 1% sustained, shared pool free memory below 5%, and library cache: mutex X rising into the top non-idle waits, aligned to playbook thresholds.
  • Shared pool and buffer cache resize history from V$SGA_DYNAMIC_COMPONENTS, surfacing ASMM oscillation that often precedes ORA-04031.
  • ML-based anomaly detection on parse rate and wait event distributions, which can surface slow drift toward version-count explosion before thresholds trip.
  • Alert log scraping that surfaces ORA-04031 the moment it appears, alongside the wait-event and parse-rate context that explains why.

These signals are available in Oracle Database monitoring with Netdata.