Oracle ORA-04036 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04036: PGA Memory Used by the Instance Exceeds PGA_AGGREGATE_LIMIT

ORA-04036 is raised when the total PGA (Program Global Area) memory consumed across all sessions in an Oracle instance surpasses the hard limit defined by the PGA_AGGREGATE_LIMIT parameter. Introduced in Oracle 12c, this safeguard prevents uncontrolled PGA growth from exhausting the entire system's memory. When the limit is breached, Oracle forcibly terminates or aborts the call of the session consuming the most PGA memory.

Top 3 Causes

1. PGA_AGGREGATE_LIMIT Set Too Low

The default value of PGA_AGGREGATE_LIMIT is the greater of twice PGA_AGGREGATE_TARGET or 2GB. If a DBA has manually set this too conservatively, even moderate workloads can trigger the error.

-- Check current PGA parameter settings
SELECT name, value
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit');

-- Increase the limit dynamically (example: set to 16GB)
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 16G SCOPE = BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 8G  SCOPE = BOTH;

2. Runaway Sessions with Excessive PGA Consumption

Poorly written PL/SQL code with unclosed cursors, large in-memory collections, or long-running sort operations can cause individual sessions to accumulate enormous amounts of PGA, pushing the instance over the limit.

-- Identify top PGA-consuming sessions
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.program,
    ROUND(p.pga_used_mem  / 1024 / 1024, 2) AS pga_used_mb,
    ROUND(p.pga_alloc_mem / 1024 / 1024, 2) AS pga_alloc_mb
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE s.username IS NOT NULL
ORDER BY p.pga_used_mem DESC
FETCH FIRST 10 ROWS ONLY;

-- Kill the offending session if necessary
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

3. Spike in Parallel Queries or Large Sort/Hash Join Operations

Each parallel slave process receives its own PGA allocation, so a sudden surge of parallel queries or bulk batch jobs with heavy sorting and hash joins can multiply PGA usage rapidly.

-- Check active work areas (sorts, hash joins, etc.)
SELECT
    operation_type,
    work_area_size / 1024 / 1024  AS workarea_mb,
    number_passes,
    active_time_seconds
FROM v$sql_workarea_active
ORDER BY work_area_size DESC;

-- Review overall PGA statistics
SELECT name, value / 1024 / 1024 AS value_mb
FROM v$pgastat
WHERE name IN (
    'total PGA inuse',
    'total PGA allocated',
    'maximum PGA allocated',
    'aggregate PGA target parameter'
);

Quick Fix Solutions

Raise the limit immediately if workload has legitimately grown:

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 20G SCOPE = BOTH;

Kill the top PGA consumer to release memory fast and restore service.
Review and tune high-memory SQL — add appropriate indexes to avoid large sorts, or reduce parallel degree.

-- Reduce parallel degree for a specific table
ALTER TABLE large_table PARALLEL 2;

-- Or hint a specific query
SELECT /*+ NO_PARALLEL */ * FROM large_table ORDER BY col1;

Prevention Tips

Monitor PGA usage proactively. Set up alerts when total PGA allocated in V$PGASTAT crosses 80% of PGA_AGGREGATE_LIMIT. Use AWR reports (with STATISTICS_LEVEL = TYPICAL) to track PGA trends over time and right-size the limit before issues occur.
Enforce coding standards. Always close cursors explicitly in PL/SQL, free large collections after use (collection.DELETE), and conduct SQL reviews before deploying batch jobs to production. Schedule heavy batch workloads during off-peak hours to spread the PGA consumption peak.

Related Errors

Error Code	Description
ORA-04030	Individual process cannot allocate memory — process-level counterpart to ORA-04036
ORA-04031	Insufficient memory in SGA shared pool or large pool
ORA-01555	Snapshot too old — often co-occurs with long-running, high-memory queries

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community