Oracle hard parse storm: literal SQL, bind variables, and shared pool churn

CPU is pegged. The top non-idle wait event is library cache: mutex X or cursor: pin S wait on X. Active sessions are climbing, but logical reads are flat or falling: sessions are burning CPU on parsing, not execution. Transactions per second is unstable or declining. If the storm runs long enough, ORA-04031 starts appearing in the alert log.

This is the parse storm pattern. It is almost never a database bug. It is an application pattern meeting Oracle’s shared SQL model.

Oracle hard-parses every SQL statement whose text does not byte-for-byte match an existing cursor in the shared pool. Hard parsing compiles the statement: syntax check, semantic check, optimization, and plan generation. It is CPU-expensive and takes exclusive latches and mutexes on the library cache. With the default CURSOR_SHARING=EXACT, two statements differing only in a literal value are two distinct statements, each requiring a full hard parse.

A healthy OLTP system hard-parses less than 1% of total parses. A parse storm can push that ratio past 20%. The rest of this article covers diagnosis, the stopgaps that buy time, and the only durable fix.

What this means

A parse storm is a positive feedback loop. Literal SQL pours in. Each unique text is a new cursor. Each new cursor requires a hard parse, which holds exclusive library cache mutexes. Sessions queue behind those mutexes. CPU saturates on parse work, not business logic. Meanwhile, the shared pool fills with thousands of near-duplicate cursors that will never be reused, fragmenting memory into chunks too small for the next allocation.

The loop ends one of three ways. The application stops sending new literal variants. The shared pool runs out of contiguous free memory and starts raising ORA-04031. Or you intervene with CURSOR_SHARING=FORCE and break the loop by making Oracle rewrite literals into binds itself.

flowchart TD
    A[Application sends literal SQL] --> B[Each unique text needs hard parse]
    B --> C[Exclusive library cache mutex]
    C --> D[CPU burned on parsing]
    C --> E[Shared pool fills with one-shot cursors]
    D --> F[library cache mutex X waits rise]
    E --> G[Shared pool fragmentation]
    G --> H{Free chunk too small for next alloc?}
    H -- yes --> I[ORA-04031 allocation failure]
    H -- no --> J[Old plans age out]
    J --> B

The parse count (hard) statistic in V$SYSSTAT is the heartbeat of this pattern. Sample it twice, a few seconds apart, divide by the interval, and you have hard parses per second. Pair it with parse count (total) to get the hard parse ratio. Anything above 5% on an OLTP workload is a problem. Above 20% is severe.

Common causes

The first row is by far the most common. ORMs and dynamic SQL builders that concatenate values into SQL strings are the classic offender.

Quick checks

All of these are read-only and safe to run on production.

-- 1. Sample hard parse rate. Run twice, N seconds apart. Rate = (v2 - v1) / N.
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE 'parse count%';

-- 2. Hard parse ratio components. Healthy OLTP is <1%. >5% is a problem.
SELECT
  (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'parse count (hard)') AS hard,
  (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'parse count (total)') AS total
FROM DUAL;

-- 3. Top wait events by time waited. Look for library cache / cursor / latch events.
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO
FROM V$SYSTEM_EVENT
WHERE WAIT_CLASS != 'Idle'
ORDER BY TIME_WAITED_MICRO DESC
FETCH FIRST 10 ROWS ONLY;

-- 4. Parse-related wait events specifically.
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO
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'
);

-- 5. Literal SQL fingerprint. EXECUTIONS = 1 with similar text is the smoking gun.
SELECT SQL_TEXT, SQL_ID, LAST_ACTIVE_TIME
FROM V$SQL
WHERE EXECUTIONS = 1
ORDER BY LAST_ACTIVE_TIME DESC
FETCH FIRST 20 ROWS ONLY;

-- 6. Shared pool free memory. Below 5% of pool size is ORA-04031 risk.
SELECT POOL, NAME, BYTES/1048576 AS mb
FROM V$SGASTAT
WHERE POOL = 'shared pool' AND NAME = 'free memory';

-- 7. CURSOR_SHARING value. EXACT is the default and the literal-SQL trap.
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'cursor_sharing';

-- 8. Cursor version counts. High counts indicate bind peeking or env mismatch.
SELECT SQL_ID, VERSION_COUNT, SQL_TEXT
FROM V$SQL
WHERE VERSION_COUNT > 20
ORDER BY VERSION_COUNT DESC
FETCH FIRST 10 ROWS ONLY;

-- 9. Why cursors are not sharing. One reason column per non-shareable cause.
SELECT BIND_LENGTH_UPGRADEABLE, OPTIMIZER_MODE_MISMATCH, COUNT(*) AS cursor_count
FROM V$SQL_SHARED_CURSOR
WHERE BIND_LENGTH_UPGRADEABLE = 'Y'
   OR OPTIMIZER_MODE_MISMATCH = 'Y'
GROUP BY BIND_LENGTH_UPGRADEABLE, OPTIMIZER_MODE_MISMATCH
ORDER BY cursor_count DESC;

-- 10. Check the alert log for ORA-04031.
SELECT originating_timestamp, message_text
FROM V$DIAG_ALERT_EXT
WHERE message_text LIKE '%ORA-04031%'
ORDER BY originating_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

The FETCH FIRST n ROWS ONLY syntax appears in several queries and requires Oracle 12c or later. On 11g, use WHERE ROWNUM <= n instead.

How to diagnose it

1. Confirm the storm. Sample parse count (hard) twice, 30 seconds apart. Compute hard parses per second. Then compute the hard parse ratio against parse count (total). If the ratio is above 5% on OLTP, you have a parse problem.

2. Verify it is literal SQL. Query V$SQL for EXECUTIONS = 1 ordered by LAST_ACTIVE_TIME. If you see strings of nearly identical statements differing only in a value (WHERE order_id = 41238, then WHERE order_id = 41239, and so on), literal SQL is your root cause.

3. Check for cursor version count inflation. A single SQL_ID with many child cursors means the text is shared but the optimizer environment is not. Look at V$SQL_SHARED_CURSOR for the reason code. BIND_LENGTH_UPGRADEABLE flagged means the same SQL is getting new child cursors because bind variable lengths differ between sessions. OPTIMIZER_MODE_MISMATCH means session-level optimizer settings differ. Both are application-side issues.

4. Confirm shared pool health. Check V$SGASTAT shared pool free memory. Below 5% of pool size is ORA-04031 risk. If free memory looks adequate in aggregate but ORA-04031 is still firing, the pool is fragmented: total free is fine but no single contiguous chunk is large enough.

5. Rule out the cold start. A hard parse spike in the first 10 to 30 minutes after instance restart is normal. The shared pool is empty and every SQL must hard parse once. Check V$INSTANCE.STARTUP_TIME before escalating.

6. Rule out a flush. Someone running ALTER SYSTEM FLUSH SHARED_POOL produces an identical signature to a literal SQL storm. Check DBA audit history if available.

Metrics and signals to monitor

Fixes

Short-term: CURSOR_SHARING = FORCE

This is Oracle’s documented temporary bridge, not a permanent fix. It tells the optimizer to rewrite literals in incoming SQL into system-generated binds, so WHERE id = 42 and WHERE id = 43 share a cursor.

-- WARNING: System-wide, immediate effect on all sessions. Can cause plan regressions.
-- Apply during a maintenance window if possible. Rollback: SET CURSOR_SHARING = EXACT.
ALTER SYSTEM SET CURSOR_SHARING = FORCE;
-- Scope and SID clauses apply as usual for your deployment.

Tradeoffs and cautions:

• Oracle’s Real-World Performance group recommends FORCE only as a stopgap while source code is fixed.
• It can interact badly with function-based indexes and disable star transformation. If critical queries regress after the change, that is the likely cause.
• Adaptive Cursor Sharing still applies on top of FORCE. The optimizer may still peek the rewritten binds and produce multiple child cursors. You can reduce but not eliminate version count.
• CURSOR_SHARING = SIMILAR is deprecated since 11.2 and is not a valid value in current releases. Do not attempt to set it.

Apply FORCE during a maintenance window if possible, watch the parse rate and wait profile for 15 minutes, and have a rollback plan (set back to EXACT).

Mid-term: identify and fix the offending SQL

Use the literal SQL fingerprint query to find the worst offenders. The fix is application-side: parameterized queries, prepared statements, or ORM configuration that emits binds instead of concatenated values. Every major language and ORM supports this. The change is mechanical once you identify the hot SQL.

If the offender is a third-party application you cannot modify, FORCE is your long-term reality. Document the setting and the side effects.

Long-term: bind variables everywhere

Bind variables are the only durable fix. They are how Oracle’s shared SQL model is meant to be used. With binds, the application sends WHERE id = :1 and supplies the value separately. One cursor, many executions, one parse. Hard parse ratio drops back under 1%, mutex contention disappears, shared pool stabilizes.

This is a code-level change. It will not be quick on a large application. Plan it as engineering work, not as a database tuning exercise.

Never: routine ALTER SYSTEM FLUSH SHARED_POOL

Flushing the shared pool produces a hard parse storm by design: every cached plan is discarded and must be re-parsed on next execution. On a busy OLTP system during peak hours, this is self-inflicted damage. Flushing is almost never the right action and is reserved for one-time interventions against documented shared pool corruption.

Prevention

• Make hard parse ratio a tracked metric. Trend it weekly. A creeping ratio means a new code path is leaking literals. Catch it before it becomes a storm.
• Code review for bind usage. Any string concatenation building SQL is a candidate. Static analysis tooling can catch this in CI.
• ORM configuration. Most ORMs default to parameterized queries but can be pushed into literal mode by misuse, for example inline value substitution in raw query fragments.
• Cursor version count monitoring. Alert on SQL_IDs with VERSION_COUNT above a threshold (50 or 100, depending on workload). High version count is a sign that even bind-using SQL is not sharing properly.
• Shared pool sizing review. Under ASMM, watch V$SGA_DYNAMIC_COMPONENTS for oscillating resizes. If shared pool and buffer cache are constantly stealing from each other, SGA_TARGET is too small.
• Train operators that flush is not a tuning knob. The shared pool flush belongs in the same category as killing sessions at random: occasionally justified, usually destructive.

How Netdata helps

• Per-second collection of parse count (total) and related activity from V$SYSSTAT makes parse-rate change visible. The default Oracle collector activity chart exposes total parses, executes, commits, and rollbacks; add hard-parse-specific or custom SQL collection when you need hard-parse ratio as a first-class metric.
• Wait-class telemetry shows time spent in the relevant concurrency/application wait classes; individual events such as library cache: mutex X and cursor: pin S wait on X should be confirmed with V$SYSTEM_EVENT or custom collection when event-level attribution is required.
• The top-queries and running-queries functions use V$SQLSTATS and V$SESSION to expose parse calls and active SQL, helping identify literal SQL or parsing-heavy statements.
• Other shared-pool, cursor-version, alert-log, and RAC-correlation checks remain operational queries to run alongside the collector unless your deployment adds custom metrics.

Netdata’s Oracle Database monitoring with Netdata 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 archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• 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