Why Oracle Database 26ai Hard Parses Are Destroying Your Performance And How to Stop Them

The Problem

A production OLTP database was supporting 150 concurrent users comfortably. One morning CPU jumped from 35% to 90%. Response times tripled. Nothing new was deployed.

After checking Active Session History, I saw hundreds of sessions stuck on library cache lock and latch contention the classic signs of a parse storm.

The cause? A developer had replaced bind variables with string concatenation in a product lookup query. On a system doing 1,000 lookups per minute, that meant 1,000 hard parses per minute instead of one. The shared pool was churning. The database was spending most of its CPU time just parsing SQL instead of executing it.

The Cause: Hard Parse vs Soft Parse

Every SQL statement Oracle receives goes through a parse call before execution. The parse has three checks:

1. Syntax Check Is the SQL grammatically valid?

sql

-- Fails syntax check immediately
SELECT * FORM employees;
-- ORA-00923: FROM keyword not found where expected

2. Semantic Check Do the referenced objects actually exist?

sql

-- Valid syntax but fails semantics
SELECT * FROM nonexistent_table;
-- ORA-00942: table or view does not exist

3. Shared Pool Check Has this exact SQL been parsed before?

Oracle generates a hash value for every SQL statement and searches the shared pool Oracle's SQL cache. If it finds a match, it reuses the compiled code. That is a soft parse. If it finds no match, it must do everything from scratch a hard parse.

Here is what that difference looks like in real numbers:

Parse Type	Optimization Runs?	Latch Contention	CPU Cost	Max Users (4-CPU server)
Hard Parse	Yes full CBO	High	Very high	60
Soft Parse	No reuses plan	Low	Low	150
No Parse (pre-parsed)	No	None	Minimal	270

These numbers come directly from Oracle's SQL Tuning Guide. The difference between 270 users and 60 users on identical hardware is entirely caused by how the application handles parsing.

What Forces a Hard Parse?

There are four main triggers:

1. New SQL never seen before — first execution always hard parses.

2. SQL aged out of shared pool — shared pool too small, SQL was evicted.

3. Different optimizer environment — same SQL text, but different session settings force a new hard parse:

sql

-- These three executions all force separate hard parses
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
SELECT * FROM sh.sales;    -- hard parse #1

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT * FROM sh.sales;    -- hard parse #2

ALTER SESSION SET SQL_TRACE = TRUE;
SELECT * FROM sh.sales;    -- hard parse #3

4. Literal values instead of bind variables the most common and most destructive cause:

sql

-- Each of these is a DIFFERENT SQL statement = separate hard parse
SELECT * FROM orders WHERE customer_id = 1001;
SELECT * FROM orders WHERE customer_id = 1002;
SELECT * FROM orders WHERE customer_id = 1003;
-- 50,000 customers = 50,000 hard parses, 50,000 entries in shared pool

-- This is ONE SQL statement one hard parse, soft parses forever after
SELECT * FROM orders WHERE customer_id = :customer_id;

The Commands: How to Diagnose It

Step 1 — Check Overall Hard Parse Rate

sql

-- Hard parses / total parses > 10% means you have a problem
SELECT name, value
FROM   v$sysstat
WHERE  name IN (
  'parse count (total)',
  'parse count (hard)',
  'execute count'
)
ORDER BY name;

Step 2 — Find the SQL Causing Hard Parses

sql

-- High parse_calls relative to executions = parsing too often
-- version_count > 5 = multiple child cursors = environment issues

SELECT sql_id,
       parse_calls,
       executions,
       ROUND(parse_calls / NULLIF(executions, 0), 2) AS parse_ratio,
       version_count,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sql
WHERE  parse_calls > 100
AND    executions  > 0
AND    ROUND(parse_calls / NULLIF(executions, 0), 2) > 0.5
ORDER BY parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

Step 3 — Find the Literal Value Flood

sql

-- Same first 80 characters but hundreds of versions = literal problem
SELECT SUBSTR(sql_text, 1, 80) AS sql_sample,
       COUNT(*)                 AS version_count,
       SUM(parse_calls)         AS total_parses
FROM   v$sql
WHERE  command_type = 3
GROUP BY SUBSTR(sql_text, 1, 80)
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC
FETCH FIRST 20 ROWS ONLY;

Step 4 — Check Library Cache Health

sql

-- Hit ratio below 95% = shared pool under pressure
SELECT namespace,
       gets,
       gethits,
       ROUND(gethits / NULLIF(gets, 0) * 100, 2) AS hit_ratio_pct,
       reloads
FROM   v$librarycache
WHERE  namespace = 'SQL AREA';

Step 5 — Confirm with Wait Events

sql

-- library cache lock or latch: library cache = parse storm confirmed
SELECT event,
       COUNT(*) AS sessions,
       ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 1/24
AND    event IS NOT NULL
GROUP BY event
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;

The Solution

Fix 1 — Use Bind Variables (The Only Permanent Fix)

sql

-- WRONG: unique SQL per customer = hard parse storm
SELECT * FROM orders WHERE customer_id = 12345;

-- CORRECT: one SQL for all customers = one hard parse forever
SELECT * FROM orders WHERE customer_id = :customer_id;

In PL/SQL dynamic SQL, the same rule applies:

sql

DECLARE
  v_count NUMBER;
  v_dept  NUMBER := 10;
BEGIN
  -- WRONG: hard parse on every block execution
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees WHERE department_id = ' || v_dept
    INTO v_count;

  -- CORRECT: one hard parse, reused every time
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees WHERE department_id = :dept'
    INTO v_count
    USING v_dept;
END;
/

Fix 2 — CURSOR_SHARING = FORCE (Emergency Only)

If you cannot change application code immediately third-party software, long release cycle use this as a temporary emergency fix:

sql

-- Apply immediately to stop the bleeding
ALTER SYSTEM SET cursor_sharing = FORCE;

-- Revert once application is fixed
ALTER SYSTEM SET cursor_sharing = EXACT;

CURSOR_SHARING = FORCE tells Oracle to automatically replace literal values with bind variables before checking the shared pool. It is effective but has side effects suboptimal plans in some cases, harder-to-read monitoring output. Use it only temporarily.

Fix 3 — Increase Shared Pool Size

If frequently-used SQL is being aged out even with bind variables, the shared pool may be too small:

sql

-- Check free memory in shared pool
SELECT name,
       ROUND(bytes / 1024 / 1024, 1) AS mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
AND    name IN ('free memory', 'sql area', 'library cache')
ORDER BY bytes DESC;

-- Increase if using Automatic Memory Management
ALTER SYSTEM SET sga_target = 4G SCOPE = SPFILE;

Real Example: The Full Diagnosis

Here is exactly what I ran on that production system:

Check the hard parse rate:

sql

SELECT name, value FROM v$sysstat
WHERE name IN ('parse count (total)', 'parse count (hard)');

Result: 847,293 total parses, 412,847 hard parses 48.7% hard parse ratio. That is catastrophic.

Find the offending SQL:

sql

SELECT SUBSTR(sql_text, 1, 80), COUNT(*)
FROM v$sql
WHERE sql_text LIKE '%product_lookup%'
GROUP BY SUBSTR(sql_text, 1, 80)
HAVING COUNT(*) > 50
ORDER BY COUNT(*) DESC;

Result: One query with 12,847 versions one per unique product code literal.

Apply emergency fix:

sql

ALTER SYSTEM SET cursor_sharing = FORCE;

After 10 minutes: hard parse ratio dropped to 0.32%. CPU dropped from 90% to 38%.

Developer fixed the application to use prepared statements with bind variables. After deployment:

sql

ALTER SYSTEM SET cursor_sharing = EXACT;

System ran normally at its full 150-user capacity.

Key Takeaways

Hard parses are expensive because they run the full cost-based optimizer while holding latches that block other sessions. The #1 cause is literal values in SQL instead of bind variables. One developer change can cut your database capacity from 270 users to 60 users on identical hardware.

Diagnose with: V$SYSSTAT for parse ratio → V$SQL for offending statements → V$ACTIVE_SESSION_HISTORY for wait events.

Fix permanently with: Bind variables in application code.

Fix temporarily with: CURSOR_SHARING = FORCE while the real fix is being deployed.

Next in This Series

Post 3 covers reading and comparing Oracle execution plans the primary diagnostic tool for manual SQL tuning. I will show you how to read every column, understand the row source tree, compare plans side by side, and pinpoint exactly which step is causing a performance problem.

Source: Oracle AI Database SQL Tuning Guide, Release 26ai, G43585-02, January 2026.

Written by Thiwanka Senarathna Oracle ACE Apprentice, Senior Oracle DBA specializing in Oracle EBS, SQL Tuning, and Oracle Database Administration.