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

CauseWhat it looks likeFirst thing to check
Literal SQL from the applicationThousands of cursors in V$SQL with EXECUTIONS = 1, differing only in literal valuesSELECT SQL_TEXT FROM V$SQL WHERE EXECUTIONS = 1 ORDER BY LAST_ACTIVE_TIME DESC FETCH FIRST 20 ROWS ONLY
CURSOR_SHARING changed from FORCE to EXACTSudden onset after a parameter change or maintenance windowSELECT VALUE FROM V$PARAMETER WHERE NAME = 'cursor_sharing'
Shared pool flushHard parse rate spikes to near 100% immediately after ALTER SYSTEM FLUSH SHARED_POOLRecent DBA audit history or V$SQL first load times clustered post-flush
New application deploymentOnset correlates with a code push; new SQL patterns appearCompare deploy timestamps against parse rate charts
Shared pool undersizedPlans age out under normal load, forcing reparse; ASMM oscillating between componentsV$SGASTAT shared pool free memory below 5%
High cursor version countOne parent cursor with many children; bind peeking or session optimizer environment differencesV$SQL.VERSION_COUNT and V$SQL_SHARED_CURSOR reason columns

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

SignalWhy it mattersWarning sign
Hard parse ratio (parse count (hard) / parse count (total))Direct measure of unique-compilation work versus reuseSustained above 1% on OLTP warrants investigation; above 5% is a problem; above 20% severe
library cache: mutex X wait timeExclusive mutex held during hard parse; rising wait time means sessions queueing on parseGreater than 5% of DB time, or growing trend
cursor: pin S wait on X wait timeVariant of the same contention, often with high version countSustained non-trivial total wait time
parse count (hard) rateAbsolute throughput of unique compilationsAbove 100 per second on most systems causes contention
Shared pool free memory (V$SGASTAT)Leading indicator of pool pressureBelow 5% of pool size, or declining trend
ORA-04031 in alert logHard cliff: allocation failedAny occurrence is urgent
CPU utilizationParsing is CPU-bound; high CPU with low logical reads is the parse signaturePegged cores with low buffer gets per second
V$SQL high version countBind peeking or environment mismatch preventing share even when text matchesPer-parent cursor counts above 100
TPS deviationCascading impact: more time parsing means less time committingSustained drop from baseline

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 (hard) and parse count (total) makes the hard parse ratio a live signal, not a guess. Spikes from a new deployment show up within seconds.
  • Wait event telemetry surfaces library cache: mutex X and cursor: pin S wait on X alongside CPU utilization, so the parse signature (high CPU, low logical reads, mutex waits) is visible in one pane.
  • Shared pool free memory and ORA-04031 occurrences from the alert log correlate directly with hard parse spikes, so you can confirm the cliff approach before it hits.
  • ML anomaly detection on hard parse ratio catches the slow creep of new literal SQL before it becomes an incident.
  • Cursor version count per SQL_ID, tracked over time, flags bind peeking and optimizer environment mismatches that prevent cursor sharing.
  • Correlation across instances in RAC shows whether the parse storm is cluster-wide or concentrated on one instance, which narrows the application code path to investigate.

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