ORA-04031: Shared Pool Exhaustion — Root Cause and Fix

Posted on by sanjeeva
Oracle shared pool memory fragmentation causing ORA-04031 error diagnosed using V$SGASTAT and V$SHARED_POOL_RESERVED on Oracle production database

In the life of an Oracle Database Expert, It usually early morning when the monitoring alert fired. Applications throwing ORA-04031. Developers calling. The on-call DBA logged in to find the shared pool exhausted — Oracle unable to allocate a single new object in memory. No new SQL could parse. No new PL/SQL could execute. The database was effectively frozen for anything that required a fresh parse.

ORA-04031 is one of those errors that looks like a memory problem on the surface but is almost always a hard parse problem underneath. Throwing more memory at it without fixing the parse behavior is a temporary patch — the pool fills up again within days.

We have seen this error on databases with 4 GB shared pools and on databases with 64 GB shared pools. Size alone is not the answer. Let us diagnose it properly.

What ORA-04031 Actually Means

ORA-04031: unable to allocate %s bytes of shared memory
("%s","%s","%s","%s")

The four substitution variables in the full error message are your first diagnostic clue:

ORA-04031: unable to allocate 4096 bytes of shared memory
("shared pool","SELECT * FROM orders WHERE...","sql area","KGL heap")
  • Bytes requested — how much memory Oracle tried to allocate
  • Pool name — shared pool, large pool, java pool, or streams pool
  • Object type — sql area, PL/SQL area, library cache
  • Heap type — KGL heap, KGH heap — internal Oracle memory structures

When the pool name is shared pool and the object type is sql area or KGL heap — that is a hard parse storm. That is the most common production cause.

Step 1 — Confirm Shared Pool Fragmentation

SELECT name,
       ROUND(bytes/1024/1024, 2) AS size_mb,
       ROUND(bytes * 100 /
             SUM(bytes) OVER (PARTITION BY pool), 2) AS pct_of_pool
FROM   v$sgastat
WHERE  pool = 'shared pool'
ORDER  BY bytes DESC
FETCH  FIRST 10 ROWS ONLY;

NAME                          SIZE_MB  PCT_OF_POOL
----------------------------  -------  -----------
free memory                      8.42         4.21
library cache                  142.18        71.09
sql area                        38.64        19.32
row cache                        8.12         4.06
miscellaneous                    2.64         1.32

What to look for:

  • free memory below 5% of the total shared pool is a warning sign — you are close to exhaustion
  • library cache consuming over 70% with low free memory means SQL is accumulating and not being aged out
  • After ORA-04031 fires, free memory is typically near zero or shows heavily fragmented small chunks

Check fragmentation directly:

SELECT request_misses,
       request_failures,
       ROUND(free_space/1024/1024,2)     AS free_mb,
       ROUND(avg_free_size/1024,2)       AS avg_free_kb,
       ROUND(max_free_size/1024,2)       AS max_free_kb,
       ROUND(used_space/1024/1024,2)     AS used_mb
FROM   v$shared_pool_reserved;

REQUEST_MISSES  REQUEST_FAILURES  FREE_MB  AVG_FREE_KB  MAX_FREE_KB  USED_MB
--------------  ----------------  -------  -----------  -----------  -------
          2341               847     7.95         4.32        12.14   192.05

REQUEST_FAILURES of 847 confirms the reserved area has been unable to satisfy contiguous allocation requests. MAX_FREE_KB of 12.14 KB tells us the largest contiguous free block available is only 12 KB — if Oracle needs to load a PL/SQL package or a large SQL statement requiring 64 KB or more, it fails immediately regardless of the 7.95 MB total free. That is classic fragmentation — the direct cause of ORA-04031 even when the pool shows free memory in aggregate.

Step 2 — Find the Hard Parse Culprits

Hard parsing happens when Oracle cannot find a matching SQL statement in the library cache and must parse from scratch. Every unique literal value in a SQL statement creates a separate entry — a separate hard parse.

SELECT sql_id,
       parse_calls,
       executions,
       ROUND(parse_calls * 100 /
             NULLIF(executions, 0), 2)    AS parse_ratio_pct,
       ROUND(sharable_mem/1024/1024, 2)   AS mem_mb,
       SUBSTR(sql_text, 1, 80)            AS sql_snippet
FROM   v$sql
WHERE  parse_calls > 100
AND    ROUND(parse_calls * 100 /
             NULLIF(executions, 0), 2) > 80
ORDER  BY parse_calls DESC
FETCH  FIRST 10 ROWS ONLY;

SQL_ID        PARSE_CALLS  EXECUTIONS  PARSE_RATIO_PCT  MEM_MB  SQL_SNIPPET
------------  -----------  ----------  ---------------  ------  ---------------------------
8gy3q2p1mxzt       18420       18431            99.94    0.02   SELECT * FROM orders WHERE
                                                                 customer_id = 10045
2hk9r1t4nvwq       16384       16390            99.96    0.02   SELECT * FROM orders WHERE
                                                                 customer_id = 10046
f3mn8p7xyzt1       14201       14208            99.95    0.02   SELECT * FROM orders WHERE
                                                                 customer_id = 10047

This output exposes the problem immediately. Three versions of the same logical query — SELECT * FROM orders WHERE customer_id = ? — each with a literal value, each parsed almost every time it executes. The application is not using bind variables. Oracle cannot reuse any of these execution plans.

Check the total number of unique SQL statements in the library cache:

SELECT COUNT(*) AS total_sql_statements
FROM   v$sql;

TOTAL_SQL_STATEMENTS
--------------------
               84217

84,000 SQL statements in the library cache on a database where the application logic covers perhaps 500 distinct query patterns — that is a bind variable problem filling the shared pool with tens of thousands of single-use statements.

Step 3 — Immediate Relief Under Production Pressure

When ORA-04031 fires and the database is actively impacted, we need the pool cleared before we fix the root cause. Two options depending on the severity:

Option 1 — Flush the shared pool (use with caution on busy systems):

ALTER SYSTEM FLUSH SHARED_POOL;

This clears all non-pinned objects from the shared pool immediately. It will cause a temporary spike in hard parses as Oracle repopulates the cache — expect a brief CPU spike after execution. Do not run this on a busy OLTP system during peak hours without understanding the reparsing impact.

Option 2 — Increase SHARED_POOL_RESERVED_SIZE for fragmentation relief:

-- Check current reserved area
SELECT request_failures              AS failed_allocations,
       ROUND(free_space/1024/1024,2) AS free_mb,
       ROUND(avg_free_size/1024,2)   AS avg_free_kb,
       ROUND(max_free_size/1024,2)   AS max_free_kb,
       ROUND(used_space/1024/1024,2) AS used_mb
FROM   v$shared_pool_reserved;

FAILED_ALLOCATIONS  FREE_MB  AVG_FREE_KB  MAX_FREE_KB  USED_MB
------------------  -------  -----------  -----------  -------
               847     7.95         4.32        12.14   192.05

847 failed allocations with a maximum free block of 12.14 KB confirms the reserved area is fragmented and under sustained pressure. Increase the reserved size — note this is a static parameter requiring a database restart:

ALTER SYSTEM SET shared_pool_reserved_size = 32M SCOPE = SPFILE;
-- Static parameter — requires database restart to take effect

Step 4 — The Permanent Fix

Flushing the pool and sizing the reserved area are maintenance actions, not fixes. The fix is eliminating unnecessary hard parses.

Fix 1 — Enable CURSOR_SHARING as a bridge (not a permanent solution):

-- Check current setting
SELECT value FROM v$parameter WHERE name = 'cursor_sharing';

VALUE
-----
EXACT

ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;

CURSOR_SHARING = FORCE instructs Oracle to replace literal values with bind variables automatically — WHERE customer_id = 10045 becomes WHERE customer_id = :SYS_B_0. This reduces hard parsing immediately without application changes.

Use this as a bridge while the application team implements proper bind variables. It is not a permanent architectural answer — it adds overhead per parse call and can interfere with the optimizer’s ability to use column histograms accurately.

Fix 2 — Application-level bind variable implementation (the real fix):

-- Hard parse version (the problem):
SELECT * FROM orders WHERE customer_id = 10045;
SELECT * FROM orders WHERE customer_id = 10046;

-- Bind variable version (the fix):
SELECT * FROM orders WHERE customer_id = :customer_id;

One shared plan. One library cache entry. Every execution reuses the same cursor. This is the root cause fix.

Fix 3 — Right-size SHARED_POOL_SIZE:

-- Check current shared pool size
SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS shared_pool_gb
FROM   v$sgastat
WHERE  pool = 'shared pool';

SHARED_POOL_GB
--------------
          2.00

Size the shared pool based on your workload’s library cache demand — not a round number chosen at installation. For OLTP systems with good bind variable discipline, 2–4 GB is typically sufficient. For systems with poor cursor reuse, no amount of shared pool size will prevent ORA-04031 from recurring.

ALTER SYSTEM SET shared_pool_size = 4G SCOPE = SPFILE;
-- Requires bounce for static SGA configurations
-- Dynamic if SGA_TARGET / MEMORY_TARGET is managing memory automatically

Step 5 — Verify After Fix

After implementing bind variables and resizing, monitor library cache hit ratio and hard parse rate:

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

NAME                     VALUE
-----------------------  -------
parse count (failures)        12
parse count (hard)          8421
parse count (total)       842341

Calculate hard parse ratio:

SELECT ROUND(
         (SELECT value FROM v$sysstat WHERE name = 'parse count (hard)') * 100 /
         NULLIF(
           (SELECT value FROM v$sysstat WHERE name = 'parse count (total)'),
         0), 2) AS hard_parse_pct
FROM   dual;

HARD_PARSE_PCT
--------------
          1.00

Hard parse ratio below 1% is a healthy target for OLTP systems. If you are coming down from a ratio of 90%+ — as in the scenario this post opened with — getting below 5% as an interim target is a meaningful improvement.

ORA-04031 in the Vector Search Context

If you followed Posts 2 and 3 of this series and are running Oracle AI Database 26ai with HNSW vector indexes, there is a specific ORA-04031 variant worth knowing. HNSW graphs compete with SQL parsing for shared pool memory when vector_memory_size is zero or undersized. The fix is the same parameter we covered in Post 3 — set vector_memory_size in SPFILE and bounce the instance. Sizing the Vector Memory Pool explicitly removes HNSW from the shared pool contention entirely.

Cleanup

No objects created in this post — all queries are read-only against V$ views.

What Is Next

This Post covers ORA-01555 — Snapshot Too Old. It shares the same pattern as ORA-04031: an error that looks like a configuration problem on the surface but has a deeper root cause in application behaviour. In ORA-01555’s case, the culprit is long-running queries running against an undersized undo tablespace — and the fix requires understanding undo retention, query duration, and transaction volume together.

Related Articles:

Seen ORA-04031 on a database with a correctly sized shared pool and proper bind variables? Drop the scenario in the comments — there are edge cases around large PL/SQL packages and Java pool contention that are worth a separate conversation.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.