Oracle Hard Parses: How to Find and Fix SQL That Refuses to Share Cursors

A practical Oracle DBA guide to diagnosing excessive hard parsing, identifying SQL statements that differ only by literals, understanding library cache pressure, and choosing between bind variables, CURSOR_SHARING, and shared pool tuning.

The AWR report is open. Hard parses are running at 180 per second. Parse time elapsed is near the top of the timed events. CPU usage is elevated despite relatively short, simple SQL. A quick inspection of V$SQL shows thousands of statements that are structurally identical — same table, same columns, same predicate shape — but each one carries a different literal in the WHERE clause and each one has been executed exactly once.

The immediate instinct is to increase the shared pool.

That would address the symptom — cursors aging out faster because the pool is crowded — but it would not address the cause. Oracle is not running out of room to store useful cursors. It is being asked to generate and store thousands of cursors that should never have existed separately.

The answer

A hard parse occurs every time Oracle cannot locate a matching, reusable cursor in the library cache and must compile the statement from scratch. When applications submit SQL containing literal values instead of bind variables, every distinct literal combination produces a distinct SQL text, a distinct SQL ID, and a distinct cursor. Oracle has no mechanism to merge those cursors after the fact.

The preferred fix is to make the SQL shareable at its source:

Application bind variables
        ↓
One reusable parent cursor per logical statement
        ↓
Soft parses on every subsequent execution
        ↓
Lower library cache pressure and shared pool consumption
        ↓
Lower parse CPU and reduced contention

Application bind variables are the correct permanent solution. CURSOR_SHARING=FORCE is a database-level mitigation when application changes cannot happen immediately. Increasing the shared pool may reduce the rate at which usable cursors are aged out, but it cannot make different SQL text identical. SQL Plan Baselines stabilise execution plans — they do not merge distinct SQL texts into a shared cursor.

What you will learn

How Oracle distinguishes hard parses from soft parses, and why the difference matters
How to measure hard-parse volume using V$SYSSTAT, AWR, and dynamic views
How to identify SQL families generating cursor proliferation through FORCE_MATCHING_SIGNATURE
When CURSOR_SHARING=FORCE is appropriate and what risks it carries
How to distinguish parent-cursor proliferation from child-cursor proliferation
Why SQL Plan Baselines and KEEP/RECYCLE buffer pools do not address hard-parse problems

Scope: Oracle Database 12c through 23ai. Applies to on-premises, OCI, Exadata, and cloud VM deployments. AWR and ASH queries require the Diagnostics Pack licence on Enterprise Edition where applicable. Parameter behaviour varies by release, patch level, and whether ASMM or AMM is in use. Test all parameter changes in a non-production environment before applying them to production. Treat numeric examples in this article as illustrative, not as universal thresholds.

The parse lifecycle

Every SQL statement Oracle executes goes through some form of parsing. The cost varies by the path taken.

SQL text submitted
        ↓
Compute SQL ID (hash of normalised SQL text)
        ↓
Search library cache for matching cursor
        ↓
Match found?
    /          \
  yes           no
   |             |
Soft parse    Hard parse
   |             |
   |             +-- syntax check
   |             +-- semantic check (object resolution)
   |             +-- privilege check
   |             +-- optimisation (generate execution plan)
   |             +-- row-source generation
   |             |
   |          Cursor stored in library cache
   |             |
   +-------------+
        |
   Execute cursor

A hard parse performs every step in the right branch. It resolves object names, checks privileges, invokes the optimiser, and builds the execution plan. On a busy OLTP system, hundreds of hard parses per second represent a measurable drain on CPU and a source of contention in the library cache.

A soft parse finds an existing cursor and reuses it. It still requires a library cache lookup and some validation, but it skips optimisation entirely. Soft parses are cheaper — not free, but substantially cheaper than hard parses.

Session cursor caching (SESSION_CACHED_CURSORS) can reduce soft-parse overhead by keeping recently used cursors open at the session level, bypassing even the library cache lookup for frequently repeated statements. This is a session-level refinement; it does not change the fundamental cursor-sharing problem when SQL text differs across statements.

Why literals create cursor proliferation

Oracle identifies cursors by their SQL text. Two statements are the same cursor only if their normalised text is character-for-character identical — same whitespace handling, same case, same everything Oracle normalises for. Literals are part of the text.

-- Three SQL IDs. Three parent cursors. Three hard parses.
SELECT customer_name FROM customers WHERE customer_id = 1001;
SELECT customer_name FROM customers WHERE customer_id = 1002;
SELECT customer_name FROM customers WHERE customer_id = 1003;

-- One SQL ID. One parent cursor. One hard parse.
-- Every subsequent call is a soft parse.
SELECT customer_name FROM customers WHERE customer_id = :customer_id;

This is not a minor efficiency difference. A system processing 50,000 customer lookups per hour generates 50,000 hard parses in the first pattern and approximately 1 in the second — the initial compilation. Every execution after that is a soft parse against the shared cursor.

Parent cursors versus child cursors. These are distinct concepts and the distinction matters for diagnosis.

A parent cursor corresponds to a unique SQL text — one SQL ID. Literal-driven proliferation generates many parent cursors because the SQL text genuinely differs.

A child cursor sits under a parent cursor and represents a specific execution context: a set of bind variable types, an optimiser environment, a user’s privileges, NLS settings, and other factors. Oracle creates a new child cursor when an existing child under that parent cannot be shared for some reason. A parent cursor may have a single child cursor, or many.

Literal-driven proliferation is a parent-cursor problem: too many distinct SQL IDs for what is logically the same statement. Child-cursor proliferation is a different problem, discussed in a later section. Fixing one does not automatically fix the other.

Diagnosing hard parsing

Snapshot parsing statistics

V$SYSSTAT accumulates statistics since instance startup. Instance-lifetime values are useful for comparison across instances but misleading as a health score for the current workload. Compare two snapshots taken around the problematic window — or use AWR if Diagnostics Pack is licensed.

SELECT name,
       value
FROM   v$sysstat
WHERE  name IN (
           'parse count (total)',
           'parse count (hard)',
           'parse count (failures)',
           'execute count',
           'session cursor cache hits'
       )
ORDER BY name;

Compute the hard-parse fraction at a point in time:

SELECT
    total.value                                        AS parse_total,
    hard.value                                         AS parse_hard,
    ROUND(hard.value / NULLIF(total.value, 0) * 100, 2) AS hard_parse_pct
FROM v$sysstat total
JOIN v$sysstat hard ON 1 = 1
WHERE total.name = 'parse count (total)'
AND   hard.name  = 'parse count (hard)';

The fraction alone is not sufficient. A system with 500 total parses and 50 hard parses has 10% hard parses; a system with 2,000,000 total parses and 400,000 hard parses also has 20% — but the second system is the one generating meaningful overhead. Always pair the fraction with the absolute rate and the workload context.

Take a second snapshot 15–30 minutes later during the same workload pattern and compute the interval delta manually, or rely on AWR if available.

AWR workload profile indicators

In the AWR report, the sections most relevant to hard-parse investigation are:

Load Profile — examine Parses/sec, Hard Parses/sec, Executes/sec. A high ratio of hard parses to executes indicates cursors are rarely reused.
Parse CPU to Parse Elapsed — a low ratio suggests contention during parsing, not just parse volume.
SQL ordered by Parse Calls — identifies the SQL IDs accumulating the most parse calls. High parse calls on a low-execution SQL ID is unusual and warrants investigation.
Library Cache Activity — Gets, Pins, Reloads, Invalidations by namespace. Elevated SQL AREA reloads mean cursors that were previously compiled had to be reloaded — either because they aged out of the shared pool or were invalidated.
Instance Efficiency Percentages — treat the library cache hit ratio as supporting context only. A ratio that looks healthy can coexist with significant hard-parse overhead when the volume of unique SQL texts is simply very high. The ratio measures reuse of what is already in the cache, not whether the cache contains the right things.

Find SQL with high parse calls

SELECT sql_id,
       executions,
       parse_calls,
       loads,
       invalidations,
       version_count,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sqlarea
WHERE  parse_calls > 0
ORDER BY parse_calls DESC
FETCH FIRST 30 ROWS ONLY;

Interpreting the columns:

PARSE_CALLS — number of times this SQL ID was submitted for parsing since it was loaded into the library cache. A cursor with executions equal to parse calls is never being reused between calls.
LOADS — number of times the cursor was compiled (full hard parse). More than one load indicates the cursor was aged out of the shared pool and had to be recompiled when encountered again.
INVALIDATIONS — number of times the cursor was marked invalid and required reparsing. Common causes: statistics gathered on a referenced table, DDL on a referenced object, DBMS_STATS with NO_INVALIDATE=FALSE.
VERSION_COUNT — number of child cursors under this parent. A high version count does not mean the parent was hard-parsed repeatedly; it means Oracle created multiple execution contexts under the same SQL text.

These columns measure different failure modes. Do not treat them interchangeably.

Identify SQL families generating literal proliferation

Different literal values produce different SQL IDs. A naive search for “high parse calls” will find the individual statements but miss the pattern — thousands of one-execution cursors that are all the same logical query.

Oracle computes two signatures for every SQL statement:

EXACT_MATCHING_SIGNATURE — hash of the normalised SQL text, case and whitespace folded. Two statements with the same structure and the same literals share a signature.
FORCE_MATCHING_SIGNATURE — hash of the statement after literals have been replaced with bind-variable placeholders. Two statements that differ only in literal values share this signature.

Group by FORCE_MATCHING_SIGNATURE to surface literal proliferation:

SELECT force_matching_signature,
       COUNT(DISTINCT sql_id)     AS sql_ids,
       SUM(executions)            AS executions,
       SUM(parse_calls)           AS parse_calls,
       MIN(SUBSTR(sql_text, 1, 120)) AS example_sql
FROM   v$sql
WHERE  force_matching_signature <> 0
GROUP BY force_matching_signature
HAVING COUNT(DISTINCT sql_id) > 1
ORDER BY sql_ids DESC
FETCH FIRST 20 ROWS ONLY;

A signature group showing 5,000 distinct SQL IDs with 5,000 total executions represents 5,000 hard parses for what is logically one statement. That is the query to fix — or the workload pattern to target with CURSOR_SHARING=FORCE.

A signature group showing 1 SQL ID with 500,000 executions is healthy cursor reuse and requires no action.

CURSOR_SHARING: when Oracle substitutes literals

CURSOR_SHARING controls whether Oracle attempts to replace literal values in submitted SQL with system-generated bind variables before computing the SQL ID.

SHOW PARAMETER cursor_sharing;

or:

SELECT name, value
FROM   v$parameter
WHERE  name = 'cursor_sharing';

CURSOR_SHARING = EXACT (default): Oracle shares cursors only when SQL text matches after normal normalisation. Literals in the text produce distinct SQL IDs.

CURSOR_SHARING = FORCE: Oracle replaces numeric and string literals with system-generated bind variables before looking up the cursor. A statement like:

SELECT customer_name FROM customers WHERE customer_id = 1001;

may be processed as something equivalent to:

SELECT customer_name FROM customers WHERE customer_id = :"SYS_B_0";

The exact internal representation is Oracle-controlled. The effect is that statements differing only in literals can share a single parent cursor.

FORCE operates at the database level and applies to all sessions. It can be overridden at the session level with ALTER SESSION SET CURSOR_SHARING = EXACT when needed.

The obsolete SIMILAR setting (removed in 12.1) is not discussed here.

Application bind variables versus CURSOR_SHARING=FORCE

Approach	Effect	Limitation
Application bind variables	One parent cursor per logical statement; soft parses on reuse; full optimiser visibility into bind values with Adaptive Cursor Sharing	Requires application code change
`CURSOR_SHARING=FORCE`	Reduces literal SQL proliferation without application change; can be applied immediately	Database-wide workaround; alters optimiser input; must be workload-tested
Increase shared pool	Retains cursors longer before aging out; reduces reload rate	Does not make different SQL text shareable; does not reduce hard-parse rate from literal proliferation
SQL Plan Baselines	Stabilises execution plans for captured SQL IDs	Does not merge distinct SQL texts; does not reduce hard-parse volume

Application bind variables are the structurally correct solution. Each logical statement compiles once. The optimiser receives a stable, typed placeholder. Adaptive Cursor Sharing (Oracle 11g and later) allows Oracle to maintain multiple child cursors under one parent when bind value distributions make it beneficial — bind-sensitive and bind-aware plans. This is not possible with cursors that have already been scattered across thousands of distinct SQL IDs by literal proliferation.

CURSOR_SHARING=FORCE is not equivalent to properly coded application bind variables, and it is not universally safe:

For highly selective predicates with significant data skew, literal-specific optimisation may have been genuinely useful. Replacing literals with system-generated binds changes what the optimiser sees.
Some application SQL frameworks and OR mappers interact with FORCE in unexpected ways.
Stored outlines, SQL patches, and some features of SQL Plan Management interact with CURSOR_SHARING behaviour.
Set it globally only after workload testing that covers the full range of statement shapes and data distributions in the environment.

Shared pool and library cache

SGA
├── Buffer Cache
│   ├── DEFAULT pool
│   ├── KEEP pool
│   └── RECYCLE pool
│
└── Shared Pool
    ├── Library Cache
    │   ├── SQL cursors (SQL AREA namespace)
    │   ├── PL/SQL units
    │   └── Object metadata
    └── Data Dictionary Cache

The library cache is the component of the shared pool that holds compiled SQL cursors, PL/SQL objects, and related metadata. It is not independently configurable via a standalone parameter in current supported Oracle releases. When DBAs say “increase the library cache,” the practical action is to increase the shared pool — which gives the library cache more room within the pool’s allocation.

Relevant parameters:

SHOW PARAMETER shared_pool_size;
SHOW PARAMETER sga_target;
SHOW PARAMETER memory_target;

When SGA_TARGET is set (Automatic Shared Memory Management), Oracle manages shared pool allocation dynamically within the SGA target. When MEMORY_TARGET is also set (Automatic Memory Management), Oracle manages both SGA and PGA allocations automatically. In both cases, explicitly setting SHARED_POOL_SIZE acts as a minimum floor for the shared pool allocation.

An undersized shared pool causes cursors to be aged out before they can be reused, increasing reload rates and contributing to additional hard parses. Fixing shared pool sizing may reduce that reload pressure — but it cannot cause two different SQL texts to become the same cursor. If the application is generating 10,000 unique SQL IDs per minute, more shared pool memory buys more space for those 10,000 unique cursors, nothing more.

Investigate shared pool sizing as a contributing factor after confirming that literal proliferation has been addressed or is under control.

Library cache diagnostics

SELECT namespace,
       gets,
       gethits,
       ROUND(gethits / NULLIF(gets, 0) * 100, 2) AS get_hit_pct,
       pins,
       pinhits,
       ROUND(pinhits / NULLIF(pins, 0) * 100, 2) AS pin_hit_pct,
       reloads,
       invalidations
FROM   v$librarycache
ORDER BY reloads DESC;

Focus on the SQL AREA and TABLE/PROCEDURE namespaces. Interpret the columns:

GETS / GETHITS — lookups and cache hits. A low get-hit ratio in the SQL AREA namespace indicates cursors are frequently not found in the cache — either because they were never loaded (literal proliferation) or because they were aged out (shared pool pressure).
PINS / PINHITS — executions and cache hits at execution time. A low pin-hit ratio indicates the object was not resident when Oracle tried to execute it.
RELOADS — the cursor or object had to be loaded again because it was aged out of the shared pool. Elevated reloads with a healthy application bind pattern suggest shared pool pressure. Elevated reloads alongside thousands of unique SQL IDs suggest the pool is crowded by cursor proliferation.
INVALIDATIONS — cursors were marked invalid, typically due to DDL, statistics collection, or DBMS_STATS. Frequent invalidations force subsequent executions to hard-parse again even if the cursor was already in the cache.

These are instance-lifetime accumulators. For interval analysis, compare two snapshots or use AWR Library Cache Activity section.

Inspect shared pool memory allocation:

SELECT pool,
       name,
       ROUND(bytes / 1024 / 1024, 1) AS mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 15 ROWS ONLY;

free memory in the shared pool represents unallocated space. A persistently low free-memory value alongside ORA-04031 errors (unable to allocate shared memory) confirms memory pressure. High sql area consumption combined with a large number of unique SQL IDs points back to cursor proliferation rather than pool undersizing.

Child cursor proliferation

Literal proliferation creates many parent cursors. A separate problem — child cursor proliferation — creates many child cursors under a single parent. The two can coexist, but they require different fixes.

Identify parents with an unusually high child count:

SELECT sql_id,
       version_count,
       executions,
       parse_calls,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sqlarea
WHERE  version_count > 20
ORDER BY version_count DESC
FETCH FIRST 20 ROWS ONLY;

Common reasons Oracle cannot share an existing child cursor include:

Bind variable type mismatch between sessions
Different optimiser environments (optimizer_mode, optimizer_features_enable)
Different NLS settings (NLS_SORT, NLS_DATE_FORMAT)
Different user privileges at the time of parsing
Statistics changes between executions (bind-sensitive plan invalidation)
Adaptive Cursor Sharing creating additional bind-aware child cursors

For detailed diagnosis, V$SQL_SHARED_CURSOR explains why a specific child cursor could not be shared with a sibling:

SELECT *
FROM   v$sql_shared_cursor
WHERE  sql_id = '&sql_id'
ORDER BY child_number;

Each column in V$SQL_SHARED_CURSOR represents one non-shareability reason. A Y in a column means that reason prevented the child from sharing an existing cursor.

Fixing literal SQL proliferation will not fix unrelated child-cursor non-shareability. Diagnose them separately.

Why SQL Plan Baselines do not fix hard parsing

SQL Plan Management captures execution plans for SQL statements and can prevent the optimiser from switching to unacceptable plans without DBA approval. It is controlled primarily by:

SHOW PARAMETER optimizer_capture_sql_plan_baselines;
SHOW PARAMETER optimizer_use_sql_plan_baselines;

Setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE allows Oracle to capture plans into DBA_SQL_PLAN_BASELINES. Each captured baseline is keyed by the SQL ID of the statement it was captured for.

The limitation for hard-parse scenarios is fundamental. A statement with literals generates a unique SQL ID for each distinct set of literals. Capturing a baseline for one SQL ID does nothing for the other 4,999 SQL IDs in the same literal family. Each of those statements still hard-parses the first time it is encountered, and each may independently capture its own baseline — compounding the management overhead rather than reducing it.

SQL Plan Baselines solve the problem: this specific SQL statement should use a stable plan even when the optimiser would choose differently. They do not solve the problem: this application is generating thousands of structurally identical SQL statements that should share one cursor. The problems are orthogonal.

Why KEEP and RECYCLE buffer pools are unrelated

SGA
├── Buffer Cache           ← stores data blocks read from disk
│   ├── DEFAULT pool       ← general-purpose block cache
│   ├── KEEP pool          ← retains specific objects in cache (avoid re-reads)
│   └── RECYCLE pool       ← large full-scan objects, aged out aggressively
│
└── Shared Pool            ← stores parsed SQL, PL/SQL, object metadata
    └── Library Cache
        └── SQL cursors

The KEEP and RECYCLE buffer pools are components of the buffer cache. The buffer cache stores copies of data blocks — the actual rows and index entries read from datafiles. The KEEP pool retains specific object blocks in memory to reduce physical I/O. The RECYCLE pool isolates large-scan workloads so they do not pollute the DEFAULT buffer cache.

Neither pool has any involvement with SQL cursor compilation, shared pool allocation, or library cache management. Adjusting DB_KEEP_CACHE_SIZE or DB_RECYCLE_CACHE_SIZE has no effect on hard-parse rate or cursor sharing.

This confusion tends to arise from conflating “memory tuning” as a category. Buffer cache tuning and shared pool tuning address different structures with different symptoms and different diagnostics.

Remediation hierarchy

Work through this sequence. Do not skip to memory sizing before establishing what the actual source of parse overhead is.

1. Confirm hard parsing is actually excessive
   — hard parses/sec from V$SYSSTAT interval delta or AWR Load Profile

2. Identify the SQL families responsible
   — V$SQLAREA ordered by parse calls
   — V$SQL grouped by FORCE_MATCHING_SIGNATURE

3. Determine whether literals are the cause
   — Many SQL IDs per FORCE_MATCHING_SIGNATURE group confirms it

4. Fix the application to use bind variables where feasible
   — Coordinate with development team
   — Prioritise highest-volume literal families first

5. If application changes cannot happen immediately, test CURSOR_SHARING=FORCE
   — Non-production first
   — Cover the full workload: OLTP, batch, reporting, mixed queries
   — Monitor for plan regressions with the same workload

6. Investigate child cursor proliferation separately
   — V$SQLAREA WHERE version_count > threshold
   — V$SQL_SHARED_CURSOR for specific SQL IDs

7. Check shared pool pressure and cursor aging
   — V$LIBRARYCACHE reloads and invalidations
   — V$SGASTAT free memory
   — ORA-04031 in the alert log

8. Resize the shared pool only when evidence shows cursor aging under memory pressure
   — Not as an initial response to high parse counts

9. Re-measure using the same workload window
   — AWR comparison report or manual V$SYSSTAT snapshots

Before-and-after validation

Measure the same workload window before and after any change. Use an AWR comparison report if Diagnostics Pack is licensed, or take manual V$SYSSTAT snapshots.

Target metrics:

Before
------
Distinct SQL IDs for logical statement  :  4,812
Executions (interval)                   :  1,200,000
Hard parses/sec                         :  185
Parse calls / Execute ratio             :  ~1.0 (every execute requires a parse)
V$LIBRARYCACHE SQL AREA reloads         :  elevated

After bind variable implementation
-----------------------------------
Distinct SQL IDs for logical statement  :  1
Executions (interval)                   :  1,240,000
Hard parses/sec                         :  3
Parse calls / Execute ratio             :  ~0.001
V$LIBRARYCACHE SQL AREA reloads         :  near zero

These numbers are illustrative. The key signals are directional: FORCE_MATCHING_SIGNATURE groups collapsing from many SQL IDs to one, hard parses dropping sharply, and executions per parse rising substantially.

If CURSOR_SHARING=FORCE was applied rather than application bind variables, verify that no execution plans have regressed for high-volume statements. Use DBMS_XPLAN.DISPLAY_CURSOR for specific SQL IDs and compare against pre-change AWR SQL plans.

Troubleshooting decision tree

Hard parses elevated?
         |
         v
Many SQL IDs per FORCE_MATCHING_SIGNATURE group?
         |
    +----+----+
    |         |
   Yes        No
    |          |
Literal        Investigate child cursors
SQL            — V$SQLAREA WHERE version_count > N
proliferation  — V$SQL_SHARED_CURSOR
    |          Investigate invalidations
    |          — DDL, stats collection, grants
    |          Investigate shared pool aging
    |          — V$LIBRARYCACHE reloads
    |          — V$SGASTAT free memory
    v
Application bind variables feasible?
    |
 +--+--+
 |     |
Yes    No (near-term)
 |     |
Fix    Test CURSOR_SHARING=FORCE
app    on non-production workload first
 |         |
 |    Regressions?
 |    Yes: evaluate per-statement workarounds
 |    No:  apply with monitoring
 |
Re-measure on same workload window

Do not do this

Do not increase the shared pool as an initial response to high hard parses without first determining whether literal SQL proliferation is the cause.
Do not assume every parse problem is a memory problem.
Do not enable OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE expecting it to reduce cursor proliferation caused by literals.
Do not adjust KEEP or RECYCLE buffer pools in response to library cache or shared pool symptoms.
Do not set CURSOR_SHARING=FORCE globally without testing the full workload pattern in a non-production environment first.
Do not evaluate parse health from a single lifetime ratio. Compute interval deltas or use AWR comparison reports.
Do not flush the shared pool in production as a troubleshooting step. ALTER SYSTEM FLUSH SHARED_POOL removes all cached cursors. Every subsequent SQL execution requires a hard parse until those cursors are rebuilt. Flushing the pool can temporarily make the parse overhead significantly worse, not better.
Do not treat session_cursor_cache_hits as evidence that parsing is healthy. Session cursor caching reduces soft-parse overhead; it has no effect on hard-parse rate caused by literal SQL.

Official references

Continue reading

Marios Pavlidis Principal Database Administrator