ORA-04031: Shared Pool Exhaustion During the Month-End Report Window

Month-end reports had always run slowly. Then month-end started failing with ORA-04031: unable to allocate shared memory. The shared pool was running out of space to store execution plans and PL/SQL compiled code. The cause: one reporting tool that generated thousands of unique SQL statements, each with a different literal value, each occupying a separate slot in the shared pool library cache.

The Alert

ORA-04031 at 22:14 during month-end batch. Shared pool at 98% utilisation. New SQL statements could not be parsed and cached. The error did not crash the database, but new sessions could not execute any SQL that required a new shared pool allocation.

The Discovery

SELECT
  sql_text,
  executions,
  sharable_mem / 1024 AS mem_kb,
  parse_calls,
  version_count
FROM   v$sql
WHERE  executions = 1                    -- run only once — never reused
ORDER BY sharable_mem DESC
FETCH FIRST 30 ROWS ONLY;

-- Also check total single-use SQL burden:
SELECT
  COUNT(*) AS single_use_count,
  SUM(sharable_mem) / 1024/1024 AS wasted_mb
FROM   v$sql
WHERE  executions = 1;

247,000 SQL statements with executions=1. 4.2GB of shared pool consumed by statements that ran once and would never run again. Each statement was identical except for a literal value: WHERE customer_id = 10047, WHERE customer_id = 10048, etc. The reporting tool was generating literal SQL instead of bind variables.

Incident Timeline

Root Cause

Oracle's shared pool caches the parsed and compiled form of every SQL statement. When the same query is run with different literal values instead of bind variables, Oracle treats them as completely different statements, parsing and caching each one separately. 247,000 variations of the same customer report query consumed 4.2GB of shared pool space with zero reuse.

The Fix

-- Emergency: enable CURSOR_SHARING to force bind variable substitution
-- Use with caution — can affect plan stability
ALTER SYSTEM SET cursor_sharing = FORCE;

-- Flush accumulated single-use cursors:
ALTER SYSTEM FLUSH SHARED_POOL;

-- Monitor shared pool health:
SELECT
  pool,
  name,
  ROUND(bytes/1024/1024, 2) AS mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

Prevention

The reporting tool was reconfigured to use bind variables — the correct long-term fix. CURSOR_SHARING was reverted to EXACT after the tool change was verified. A monthly check on v$sql for single-use SQL over 100MB total is now part of the DBA maintenance routine.

Diagnosing Shared Pool Fragmentation

ORA-04031 can have two distinct causes: not enough shared pool allocated, or too much shared pool fragmented by single-use SQL. These require different fixes. Fragmentation is the more common cause in production systems that have been running for weeks.

SELECT
  name,
  bytes / 1024 / 1024                     AS size_mb,
  CASE WHEN name = 'free memory'
    THEN 'Available for new allocations'
    ELSE 'In use'
  END                                       AS status
FROM   v$sgastat
WHERE  pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 15 ROWS ONLY;

SELECT
  COUNT(*)                             AS single_use_cursors,
  SUM(sharable_mem) / 1024 / 1024      AS wasted_mb,
  ROUND(SUM(sharable_mem) / (
    SELECT SUM(bytes) FROM v$sgastat WHERE pool = 'shared pool'
  ) * 100, 1)                            AS pct_of_shared_pool
FROM   v$sql
WHERE  executions = 1;

-- If wasted_mb > 20% of shared pool: literal SQL is the primary cause
-- If wasted_mb is small: shared pool is genuinely undersized

-- Find the pattern of single-use SQL (what is being varied):
SELECT
  REGEXP_REPLACE(sql_text, '\d+', 'N') AS normalised_pattern,
  COUNT(*)                             AS variations,
  SUM(sharable_mem)/1024               AS total_kb
FROM   v$sql
WHERE  executions = 1
GROUP BY REGEXP_REPLACE(sql_text, '\d+', 'N')
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;