ORA-04036 PGA Limit Error

 

🔴 Problem

Users suddenly report:

• Long running queries

• Sessions getting terminated automatically

• Application jobs failing

In the alert log the DBA finds the error:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Oracle automatically kills the sessions consuming the most PGA memory to protect the system.

---

📌 What Causes This Error

This happens when total PGA memory usage exceeds the value of the parameter:

PGA_AGGREGATE_LIMIT

This limit protects the server from running out of physical memory.

Common causes:

• Large sort operations

• Heavy hash joins

• Parallel queries

• Badly written SQL

• Reporting jobs scanning millions of rows

---

🔎 Investigation Steps (DBA Approach)

1️⃣ Check current PGA settings

SHOW PARAMETER pga;

Example output:

pga_aggregate_target = 4G
pga_aggregate_limit  = 6G

---

2️⃣ Check current PGA usage

SELECT name, value
FROM v$pgastat;

Important values:

• total PGA allocated

• maximum PGA allocated

---

3️⃣ Identify sessions using high PGA

SELECT
s.sid,
s.serial#,
p.pga_used_mem,
p.pga_alloc_mem
FROM v$session s, v$process p
WHERE s.paddr = p.addr
ORDER BY p.pga_used_mem DESC;

This identifies the sessions consuming the most memory.

---

4️⃣ Check SQL using large work areas

SELECT
sql_id,
operation_type,
expected_size,
actual_mem_used
FROM v$sql_workarea_active;

This shows which SQL operations are consuming PGA memory.

---

🛠 Immediate Solution

If server memory allows, increase the limit.

ALTER SYSTEM SET pga_aggregate_limit=10G SCOPE=BOTH;

Or increase the target:

ALTER SYSTEM SET pga_aggregate_target=6G SCOPE=BOTH;

---

⚠️ Important DBA Checks Before Increasing

Before increasing PGA limits, check:

• Server physical RAM

• Number of parallel queries

• Large sorts or hash joins

• Application SQL design

Also check parallel settings:

SHOW PARAMETER parallel;

Too many parallel processes can consume large PGA.

---

📊 Real Production Root Cause Example

In a production reporting system:

• A monthly report ran with parallel query

• It processed 50 million rows

• Multiple hash joins were created

• PGA usage crossed the limit

The application server involved was Apache Tomcat.

---

✅ Final Fix Implemented

1️⃣ Increased memory limits

PGA_AGGREGATE_TARGET = 8G
PGA_AGGREGATE_LIMIT  = 12G

2️⃣ Reduced parallel query degree

3️⃣ Optimized the report SQL with indexes

4️⃣ Scheduled the job during off-peak hours

---

📘 Key Learning for Oracle DBAs

Monitor these views regularly:

• v$pgastat

• v$process

• v$sql_workarea

• v$sql_workarea_active

Best practice:

PGA_AGGREGATE_LIMIT ≈ 2 × PGA_AGGREGATE_TARGET