Oracle 'cursor: pin S wait on X': mutex contention on hot cursors

Sessions accumulating on cursor: pin S wait on X want a shared (S) mutex pin on a cached cursor while another session holds an exclusive (X) pin on the same cursor object. The X holder is usually hard parsing, invalidating the cursor, or doing library cache maintenance. Waiters queue behind a single mutex.

This event sits in the library cache mutex family alongside library cache: mutex X, cursor: mutex S, and cursor: mutex X. All of them reflect contention on the shared SQL area. cursor: pin S wait on X specifically points at cursor-level pin contention, almost always driven by a small number of hot SQL IDs that are parsed, invalidated, or executed frequently, or that have accumulated an unreasonable number of child cursors.

Individual waits are measured in microseconds and should be rare. When this event climbs into the top five waits by total time waited, you are looking at one of three root patterns: a literal-SQL parse storm flooding the library cache, a high version count cursor fragmenting a single parent into hundreds of children, or cursor invalidation churn from statistics gathering or DDL. The fix is never the wait event itself. It is whatever is forcing sessions to line up on the same mutex.

What this means

Oracle uses mutexes to protect library cache objects, including parent and child cursors. A shared pin is the normal, cheap path: a session wants to execute a cached cursor, so it takes an S pin to keep the cursor pinned while it reads the plan. An exclusive pin is the expensive path: it happens during hard parse, invalidation, and certain maintenance operations where the cursor structure itself is being created, modified, or destroyed.

The wait event fires when a session requests an S pin and another session already holds an X pin on the same cursor. P1 is the hash value of the cursor. P2 is the raw mutex value, where the high bits encode the session ID holding the exclusive pin and the low bits encode the current reference count. If you decode P2, you can often identify the blocker directly without chasing BLOCKING_SESSION.

flowchart TD
    A[Session wants to execute SQL] --> B{Cursor in library cache?}
    B -- No --> C[Hard parse: acquire exclusive X pin]
    B -- Yes --> D[Soft parse: acquire shared S pin]
    C --> E[X pin held during parse or invalidation]
    D --> F{Another session holds X pin?}
    F -- Yes --> G[Wait event: cursor pin S wait on X]
    F -- No --> H[Execute cursor]
    E --> I[Release X pin]
    I --> H
    G --> J[Queue behind X holder]
    J --> H

The operational signal is this event climbing into the top wait events by aggregate time waited, with a high total wait count. Average wait per event is less useful than the aggregate, because each individual wait is tiny but the volume is enormous. When hundreds of sessions all try to pin the same handful of hot cursors, the cumulative time dominates the DB time profile and crowds out real work.

Common causes

Quick checks

Run these read-only. They confirm the event is real and point at the offending SQL.

-- Confirm the event is dominating wait time
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 (
  'cursor: pin S wait on X',
  'cursor: mutex S',
  'cursor: mutex X',
  'library cache: mutex X',
  'latch: shared pool'
)
ORDER BY TIME_WAITED_MICRO DESC;

-- Hard parse rate: sample twice, N seconds apart
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME LIKE 'parse count%';
-- Hard parses should be well under 1% of total parses

-- High version count cursors: anything over ~100 child cursors is suspect
SELECT SQL_ID, COUNT(*) AS child_count
FROM V$SQL
GROUP BY SQL_ID
HAVING COUNT(*) > 50
ORDER BY child_count DESC
FETCH FIRST 20 ROWS ONLY;

-- Current waiters right now
SELECT SID, SERIAL#, EVENT, SECONDS_IN_WAIT, SQL_ID,
       P1, P2, P3, BLOCKING_SESSION, FINAL_BLOCKING_SESSION
FROM V$SESSION
WHERE EVENT = 'cursor: pin S wait on X'
  AND STATE = 'WAITING'
ORDER BY SECONDS_IN_WAIT DESC;

-- Mutex sleep history: which cursor hash is absorbing the most sleeps
SELECT MUTEX_TYPE, LOCATION, SLEEPS, WAIT_TIME
FROM V$MUTEX_SLEEP_HISTORY
ORDER BY SLEEPS DESC
FETCH FIRST 20 ROWS ONLY;

How to diagnose it

1. Confirm the event is a meaningful share of DB time. If it is outside the top 10 waits, look elsewhere. A few microseconds per session is noise.

2. Check the hard parse rate. Sample parse count (hard) from V$SYSSTAT twice, ten seconds apart. If hard parses are more than 1% of total parses, or above roughly 100 per second on a typical OLTP system, you have a parse storm. The sibling waits (library cache: mutex X, latch: shared pool) will usually be elevated too.

3. Find the hot cursors. Query V$SQL grouped by SQL_ID to find cursors with abnormal child counts. The playbook threshold is more than 100 child cursors per parent, which points at adaptive cursor sharing, bind variable peeking, or optimizer environment differences across sessions.

4. For each high-version-count SQL_ID, inspect V$SQL_SHARED_CURSOR. This view has one column per reason a child cursor could not be shared. Look for columns set to Y: BIND_EQUIV_FAILURE, BIND_MISMATCH, OPTIMIZER_MISMATCH, LANGUAGE_MISMATCH, AUTH_CHECK_MISMATCH, and similar. The set of reasons tells you whether the problem is bind-related, optimizer-environment-related, or security-related.

5. If the event spikes after a known DBMS_STATS run or DDL window, check V$SQL.INVALIDATIONS for the affected SQL_IDs. Invalidation forces reparse, which takes the exclusive pin and blocks everyone else.

6. Decode the blocker. For sessions currently waiting, V$SESSION.P2 holds the mutex value. The high bits are the session ID of the exclusive holder. You can also use FINAL_BLOCKING_SESSION to find the root of the wait chain if the blocker is itself blocked.

7. Check shared pool health. If shared pool free memory in V$SGASTAT is under 5% of pool size, or if you see ORA-04031 in the alert log, the contention is a symptom of memory starvation, not a cursor problem.

Metrics and signals to monitor

Fixes

Literal SQL parse storm

The root cause is the application sending SQL with literal values instead of bind variables. Each unique text forces a hard parse, which takes an exclusive mutex and blocks every other session that wants to touch any cursor in that library cache bucket.

Short-term mitigation: set CURSOR_SHARING = FORCE at the session or system level. Oracle replaces literals with system-generated bind variables, collapsing near-identical SQL into shared cursors. This has side effects, primarily around bind peeking and plan stability, so test it. Do not use CURSOR_SHARING = SIMILAR, which is deprecated since 11.2.

Long-term fix: change the application to use bind variables or prepared statements. ORM-generated SQL and string-concatenated dynamic SQL are the usual offenders.

High version count cursors

When a single SQL_ID has hundreds or thousands of child cursors, every pin operation has to walk a longer chain and the mutex protecting the parent becomes a hotspot. The playbook threshold is more than 100 child cursors per parent.

Query V$SQL_SHARED_CURSOR for the SQL_ID and look at which reason columns are set. Common drivers:

• BIND_EQUIV_FAILURE from adaptive cursor sharing generating a new child for each bind equivalence class. On 12.2 and later, this can produce thousands of children for one statement. The documented workaround is to disable the relevant adaptive features: _optimizer_use_feedback, _optimizer_adaptive_cursor_sharing, and _optimizer_extended_cursor_sharing_rel. Treat this as a targeted change, not a global default, and test plan stability afterward.
• BIND_MISMATCH from varying bind lengths or types across calls.
• OPTIMIZER_MISMATCH from sessions running with different optimizer environments (different NLS settings, different OPTIMIZER_MODE, different session-level parameters).

Flashback query child cursor growth

Since 11.2.0.4, flashback queries (AS OF SCN or AS OF TIMESTAMP) do not share child cursors by design. Each execution against a different SCN or timestamp creates a new child. Under concurrent load this produces unbounded child cursor growth on a single parent, which directly drives cursor: pin S wait on X.

If your application uses flashback queries at scale, the structural fix is to limit their concurrency or rewrite them to avoid the AS OF clause where read consistency is not strictly required. There is no parameter that re-enables sharing for flashback cursors.

Cursor invalidation churn

If the event spikes after DBMS_STATS runs, the gathering job is invalidating cursors that depend on the analyzed tables. Each invalidation forces a reparse on next execution, taking an exclusive pin.

Mitigations:

• Use DBMS_STATS with NO_INVALIDATE => DBMS_STATS.AUTO_INVALIDATE (the default in modern versions) so cursors invalidate gradually over time rather than all at once.
• Pin critical plans with SQL Plan Baselines (DBMS_SPM) so reparse does not change the plan.
• Schedule stats gathering outside peak windows.

Marking a hot cursor

For a small number of known-hot cursors that cannot be fixed at the application level, Oracle supports two related mechanisms.

DBMS_SHARED_POOL.MARKHOT marks a specific cursor as hot, telling Oracle to maintain multiple copies of its mutex to reduce contention. You need the full hash value, which you derive from V$SQL joined with X$KGLOB. On RAC, run it separately on each instance where the cursor is loaded.

The hidden parameter _kgl_hot_object_copies (defaults to about half the CPU count) creates multiple copies of hot objects at a system level. A common starting value is half the CPU count. This requires an spfile change and instance restart. Because it is undocumented, treat it as a last resort and confirm it is appropriate for your version with Oracle Support.

Do not flush the shared pool

ALTER SYSTEM FLUSH SHARED_POOL is almost never the right action during a cursor pin event. It invalidates every cached cursor at once, producing a synchronized hard parse storm that makes mutex contention dramatically worse for the next 10 to 30 minutes. The only legitimate use is a documented one-time intervention for shared pool corruption, not a recurring fix.

Prevention

• Enforce bind variables in application code review. Catch literal SQL in CI before it reaches production.
• Monitor child cursor counts per SQL_ID. Alert on any parent exceeding 100 children.
• Use AUTO_INVALIDATE for statistics gathering to spread reparse load over time.
• Maintain SQL Plan Baselines for critical statements so reparse does not become plan regression.
• Watch shared pool free memory. If it drops under 10%, investigate before ORA-04031 turns mutex contention into an allocation failure.
• Track parse count (hard) as a trend, not just a threshold. A slow upward drift means new literal SQL is creeping in.

How Netdata helps

Netdata surfaces the signals that confirm and localize cursor mutex contention before it dominates DB time.

• Per-second wait event tracking shows cursor: pin S wait on X climbing in real time, correlated with the exact moment a stats job, deployment, or load shift began.
• Hard parse rate and parse ratio from V$SYSSTAT are collected continuously, so a literal SQL regression from a new application build shows up as a step change, not a postmortem discovery.
• Active session count and dominant wait class are surfaced together, making it obvious when concurrency waits crowd out CPU or I/O work.
• Shared pool free memory and SGA component resize activity are tracked alongside the wait events, so you can distinguish a cursor problem from shared pool starvation in one view.
• Top SQL by executions and buffer gets helps identify the specific SQL_ID driving the contention, which is the input every fix in this article requires.

Netdata’s Oracle Database monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle lock contention cascade: one idle session that stalls the whole application
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs