cursor: pin S wait on X - Fix Oracle Mutex Contention

cursor: pin S wait on X

Overview

Wait Event Class: Concurrency

Parameters: idn (mutex identifier — hash value of the SQL text), value (mutex value encoding holder information), where (internal location code identifying which mutex operation)

cursor: pin S wait on X occurs when a session needs a shared mutex pin on a cursor (S = shared) but another session currently holds an exclusive mutex pin (X = exclusive). The waiting session cannot proceed until the exclusive holder releases the mutex.

What Are Mutex Pins?

Oracle mutexes replaced many library cache latches starting in Oracle 10g Release 2. A mutex (mutual exclusion object) is a lightweight cursor-level locking mechanism. Unlike the old library cache latch (which protected the entire library cache), each cursor has its own mutex, allowing much finer-grained locking.

Shared pin (S): Acquired by sessions executing a cursor. Multiple sessions can hold shared pins on the same cursor simultaneously — they all execute concurrently.

Exclusive pin (X): Acquired when Oracle needs to modify a cursor’s metadata — during hard parsing, cursor invalidation, or when building a new child cursor. Only one session can hold an exclusive pin at a time.

When Contention Occurs

Contention on this event means: sessions that want to execute a cursor are blocked waiting for another session that is modifying that cursor to finish. The most common scenario is a hard parse storm — many sessions try to execute a SQL statement at the same time it is being compiled (hard parsed) by another session. All executing sessions must wait until the parse completes.

The `idn` Parameter and SQL Identification

The idn parameter is a hash of the SQL text. It uniquely identifies which cursor is contended. Multiple SQL statements with different text but the same hash (hash collision) can theoretically conflict, but this is rare. More commonly, idn maps directly to a single high-activity SQL_ID.

When This Wait Is a Problem

Thresholds

Scenario	Assessment
Rare, sub-millisecond occurrences	Normal — incidental parse overlap
Consistent appearance in AWR Top 5	Problem — systematic contention exists
Many sessions waiting on same `idn` value	Hard parse storm or high-version-count cursor
Waits correlate with application deployments	Likely cursor invalidation storm
Waits during peak load only	Likely hard parse under concurrency pressure

Impact on the Application

This event directly delays SQL execution — sessions that should be running queries are instead waiting for parse operations to complete. In severe cases, a single hard parse on a popular SQL statement can block dozens of sessions for hundreds of milliseconds each.

Diagnostic Queries

1. Current Sessions Waiting on cursor: pin S wait on X

-- Sessions waiting now, including the mutex identifier
SELECT
    sw.sid,
    sw.serial#,
    s.username,
    s.program,
    s.machine,
    s.sql_id                          AS current_sql_id,
    sw.p1                             AS mutex_idn,
    sw.p2                             AS mutex_value,
    sw.p3                             AS where_code,
    sw.seconds_in_wait
FROM v$session_wait  sw
JOIN v$session       s ON sw.sid = s.sid
WHERE sw.event = 'cursor: pin S wait on X'
ORDER BY sw.seconds_in_wait DESC;

-- Translate mutex idn to SQL_ID
-- The idn is a hash of the SQL text that maps to SQL_ID
SELECT sql_id, SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE TO_NUMBER(sql_id, 'XXXXXXXXXXXXXXXX') =
      (SELECT sw.p1 FROM v$session_wait sw WHERE sw.event = 'cursor: pin S wait on X'
       FETCH FIRST 1 ROW ONLY);
-- Note: idn-to-sql_id mapping is approximate; use V$SQL_CS_STATISTICS for exact mapping

2. High Version Count Cursors — The Primary Suspect

-- Find cursors with high version counts (top cause of this wait event)
SELECT
    sql_id,
    version_count,
    executions,
    parse_calls,
    loads,
    invalidations,
    ROUND(elapsed_time / 1000000, 2)       AS elapsed_secs,
    SUBSTR(sql_text, 1, 120)               AS sql_snippet
FROM v$sqlarea
WHERE version_count > 20
ORDER BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

3. Diagnose Why a Cursor Has Many Versions

-- Why are there so many child cursors? Use V$SQL_SHARED_CURSOR
-- This shows the reason each child cursor is NOT shared with the parent
SELECT
    sql_id,
    child_number,
    reason
FROM v$sql_shared_cursor
WHERE sql_id = '&problem_sql_id'
ORDER BY child_number;

-- Count reasons for non-sharing
SELECT
    XMLCAST(XMLQUERY('//ChildNode/Name/text()' PASSING XMLTYPE(reason)
                      RETURNING CONTENT) AS VARCHAR2(4000)) AS non_sharing_reason,
    COUNT(*) AS occurrence_count
FROM v$sql_shared_cursor
WHERE sql_id = '&problem_sql_id'
GROUP BY XMLCAST(XMLQUERY('//ChildNode/Name/text()' PASSING XMLTYPE(reason)
                            RETURNING CONTENT) AS VARCHAR2(4000))
ORDER BY occurrence_count DESC;

4. ASH History of cursor: pin S Waits

-- Historical pattern of cursor pin S waits (last 24 hours)
-- Requires Diagnostics Pack license
SELECT
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI') AS sample_minute,
    ash.sql_id,
    ash.event,
    ash.p1                                          AS mutex_idn,
    COUNT(*)                                        AS sessions_waiting
FROM v$active_session_history  ash
WHERE ash.event       = 'cursor: pin S wait on X'
  AND ash.sample_time > SYSDATE - 1
GROUP BY TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI'), ash.sql_id, ash.event, ash.p1
ORDER BY COUNT(*) DESC
FETCH FIRST 30 ROWS ONLY;

5. Hard Parse Rate and Cursor Statistics

-- Hard parse rate and cursor efficiency metrics
SELECT
    name,
    value,
    ROUND(value / (SELECT (SYSDATE - startup_time) * 3600
                   FROM v$instance), 2) AS per_hour
FROM v$sysstat
WHERE name IN (
    'parse count (hard)',
    'parse count (total)',
    'parse count (failures)',
    'cursor authentications',
    'library cache misses',
    'library cache pin hits',
    'library cache reloads'
)
ORDER BY value DESC;

-- Check cursor sharing settings
SELECT name, value, description
FROM v$parameter
WHERE name IN ('cursor_sharing', 'session_cached_cursors',
               'open_cursors', 'cursor_space_for_time');

Root Causes

1. Hard Parse Storm on a Popular SQL Statement

The most common cause. When a frequently-executed SQL statement is hard-parsed (either for the first time after startup, after a cursor flush, after a statistics update, or after a schema change), all sessions trying to execute that statement simultaneously must wait on cursor: pin S wait on X until the exclusive parse operation completes.

Why hard parse storms happen:

Post-deployment: A new application version is released; all sessions immediately try to execute new SQL that has never been parsed
After stats update: DBMS_STATS.GATHER_TABLE_STATS marks cursors using that table as invalid; the next execution hard-parses all affected cursors
After ALTER TABLE: Any DDL invalidates all cursors referencing the modified table
After ALTER SYSTEM FLUSH SHARED_POOL: Invalidates all cursors; next peak causes a massive hard parse storm
After database startup: All cursors must be parsed fresh

2. High Version Count Cursors

A cursor with a very high version count (hundreds or thousands of child cursors) causes this wait for a different reason: when Oracle needs to search through hundreds of child cursors to find one that can be shared, the scan itself holds the mutex. Meanwhile, other sessions waiting to execute that SQL cannot acquire their shared mutex.

High version counts arise from:

Bind variable peeking with bad cardinality estimates: Adaptive cursor sharing creates new child cursors for different bind value ranges
Optimizer environment differences: Sessions with different NLS_* settings, OPTIMIZER_MODE, OPTIMIZER_FEATURES_ENABLE, or CURSOR_SHARING create separate child cursors
Different SQL profiles or baselines applied to different sessions: Each configuration creates a separate child
Application doing ALTER SESSION before each query: Each different session environment requires a new child cursor

3. Cursor Invalidation Loops

An application or maintenance job that repeatedly runs DBMS_STATS.GATHER_TABLE_STATS on active tables (or runs ALTER TABLE ... COMPILE during peak hours) continuously invalidates cursors, forcing repeated hard parses. Each invalidation-reparse cycle generates cursor: pin S wait on X for all concurrent users.

4. Oracle Bug Causing Excessive Mutex Contention

Several known Oracle bugs produce artificially high cursor: pin S wait on X waits even when there is no genuine parse or version-count problem. These typically involve the mutex implementation for specific Oracle versions and are fixed by specific patches. The most notable historical examples:

Bug 8611462: Excessive cursor: pin S wait on X under high concurrency (patched in 11.2.0.3)
Bug 13397217: cursor: pin S wait on X storm after statistics gathering (11.2.0.3+)
Various 12c/19c bugs: Related to adaptive statistics and cursor invalidation

Always check My Oracle Support for the specific Oracle version when persistent mutex contention cannot be explained by parsing or version counts.

Resolution Steps

Reduce Hard Parsing — Use Bind Variables

-- The root fix for hard parse storms is always bind variable usage.
-- See the latch-free documentation for detailed bind variable patterns.

-- Quick check: what fraction of SQL is unique (non-shared)?
SELECT
    ROUND(SUM(CASE WHEN executions = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2)
        AS pct_single_execution_cursors,
    COUNT(*) AS total_cursors,
    SUM(CASE WHEN executions = 1 THEN 1 ELSE 0 END) AS single_exec_cursors
FROM v$sql
WHERE users_opening > 0 OR executions > 0;
-- If pct_single_execution_cursors is > 40-50%, bind variables are not being used

-- Identify the top literal SQL producers
SELECT
    REGEXP_SUBSTR(sql_text, '^(\S+ \S+ \S+)') AS sql_prefix,
    COUNT(*)                                    AS cursor_count,
    SUM(executions)                             AS total_executions
FROM v$sql
WHERE executions < 5  -- Rarely reused = likely literal SQL
GROUP BY REGEXP_SUBSTR(sql_text, '^(\S+ \S+ \S+)')
HAVING COUNT(*) > 10
ORDER BY cursor_count DESC
FETCH FIRST 20 ROWS ONLY;

Resolve High Version Count Cursors

-- Step 1: Identify the high-version-count SQL
SELECT sql_id, version_count, SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sqlarea WHERE version_count > 50 ORDER BY version_count DESC;

-- Step 2: Diagnose why versions are being created
-- Run the V$SQL_SHARED_CURSOR query from the Diagnostic Queries section

-- Step 3: Common fixes based on reason:

-- If reason is optimizer_mismatch (different optimizer settings per session):
-- Standardize session optimizer settings in connection pool configuration
-- Remove unnecessary ALTER SESSION SET OPTIMIZER_* calls

-- If reason is bind_equiv_failure (ACS creating too many plans):
-- Consider disabling ACS for the specific cursor using a hint:
SELECT /*+ NO_BIND_AWARE */ col1, col2 FROM table1 WHERE col1 = :b1;

-- If NLS settings are causing version proliferation:
ALTER SYSTEM SET nls_language = 'AMERICAN' SCOPE=BOTH;
ALTER SYSTEM SET nls_territory = 'AMERICA' SCOPE=BOTH;
-- Ensure all application sessions use identical NLS settings

-- Step 4: Force cursor re-creation to reset version count (temporary fix)
-- Flush a specific cursor from the shared pool:
SELECT address, hash_value FROM v$sqlarea WHERE sql_id = '&problem_sql_id';
EXEC DBMS_SHARED_POOL.PURGE('&address,&hash_value', 'C');
-- Warning: This forces a hard parse on next execution — do during off-peak

Control Statistics Gathering to Prevent Cursor Invalidation Storms

-- Use NO_INVALIDATE to defer cursor invalidation after stats gather
-- (Cursors are invalidated lazily on next hard parse, not immediately)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname      => 'YOUR_SCHEMA',
    tabname      => 'YOUR_TABLE',
    no_invalidate => TRUE  -- Defer invalidation
);

-- Or set a global preference to defer all invalidations:
EXEC DBMS_STATS.SET_GLOBAL_PREFS('NO_INVALIDATE', 'TRUE');

-- Schedule stats gathering during off-peak hours using Oracle Scheduler
-- rather than running during production peak hours

Increase Cursor Cache (Reduce Repeated Soft Parses)

-- Higher session_cached_cursors reduces how often soft parses reach the mutex
ALTER SYSTEM SET session_cached_cursors = 100 SCOPE=BOTH;

-- For Java/JDBC applications:
-- Set statement cache size on the connection pool
-- Oracle JDBC: OracleConnection.setStatementCacheSize(100)

-- Monitor cache hit rate
SELECT
    SUM(ss.value)           AS cached_cursor_hits
FROM v$sesstat   ss
JOIN v$statname  sn ON ss.statistic# = sn.statistic#
WHERE sn.name = 'session cursor cache hits';

Apply Oracle Patches for Known Bugs

-- Check current Oracle patch level
SELECT * FROM v$version;
SELECT * FROM dba_registry_sqlpatch ORDER BY action_time DESC;

-- For version-specific mutex contention bugs, check MOS:
-- Doc ID 1349015.1: cursor: pin S wait on X - Troubleshooting
-- Doc ID 786507.1: High "cursor: pin S wait on X" in 10g/11g
-- Apply the latest RU/RUR for your major release to include all bug fixes

-- Workaround for some mutex bugs (Oracle Support guidance only):
-- ALTER SYSTEM SET "_kks_use_mutex_pin" = FALSE;
-- This reverts to latch-based cursor locking (pre-10gR2 behavior)
-- Do NOT use without Oracle Support confirmation for your specific bug

Prevention & Tuning

1. Prevent Shared Pool Flushes During Production Hours

ALTER SYSTEM FLUSH SHARED_POOL is sometimes used for maintenance but causes a catastrophic hard parse storm when the application hits peak load. Never flush the shared pool during business hours. If memory pressure is causing performance issues, address it by sizing the shared pool correctly.

2. Test Application Deployments for Parse Storms

When deploying new application code, test the connection behavior: what happens when 100 sessions simultaneously execute a new SQL statement for the first time? If the application uses proper bind variables and the SQL is well-formed, the first session hard-parses and all others wait briefly. If cursor sharing is poor, all 100 sessions attempt to hard-parse, creating massive contention.

-- Track max and average version counts over time
SELECT
    TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI') AS snap_time,
    MAX(version_count)                       AS max_versions,
    ROUND(AVG(version_count), 2)             AS avg_versions,
    SUM(CASE WHEN version_count > 100 THEN 1 ELSE 0 END) AS cursors_over_100_versions
FROM v$sqlarea
WHERE users_opening > 0;
-- Run periodically and alert if max_versions exceeds threshold (e.g., 500)

4. Use SQL Plan Management to Stabilize Plans

When adaptive cursor sharing or bind variable peeking creates too many child cursors, SQL Plan Baselines stabilize execution plans and reduce child cursor proliferation:

-- Create a SQL Plan Baseline for a specific SQL
DECLARE
    l_plans PLS_INTEGER;
BEGIN
    l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id => '&problem_sql_id'
    );
    DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);
END;
/

-- Verify baseline was created
SELECT sql_handle, plan_name, accepted, enabled, fixed, autopurge
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%&search_term%';

latch free — Latches are the predecessor mechanism to mutexes; latch: library cache is the older equivalent of this event
buffer busy waits — Block-level contention; similar serialization pattern but at the data block level
enq: TX - row lock contention — Row-level serialization; different mechanism but same impact: sessions waiting for another session to release a resource
log file sync — High commit rates that flush the shared pool indirectly cause hard parse storms