🔴 Problem

Users complain that:

Queries are running very slowly
Some sessions are consuming very high memory
Server memory usage is extremely high
Database performance suddenly drops

During investigation, the DBA finds excessive PGA memory usage.

Sometimes errors like:


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

📌 What is PGA

PGA (Program Global Area) is private memory allocated for each server process.

It is used for:

Sorting operations
Hash joins
Bitmap merge
Session memory
SQL work areas

Unlike SGA, PGA is not shared between sessions.

🔎 Investigation Steps

1️⃣ Check PGA parameters


show parameter pga;

Example output:


pga_aggregate_target = 2G
pga_aggregate_limit  = 4G

2️⃣ Check PGA usage


SELECT name, value
FROM v$pgastat;

Important metrics:

total PGA allocated
maximum PGA allocated
cache hit percentage

3️⃣ Identify sessions consuming 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 shows which session is using the most PGA.

4️⃣ Check SQL causing large sorts


SELECT sql_id,
       executions,
       disk_reads,
       buffer_gets
FROM v$sql
ORDER BY disk_reads DESC;

Large sorts or hash joins often consume high PGA.

🛠 Solution

Increase PGA target


ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=BOTH;

If limit is too small:


ALTER SYSTEM SET pga_aggregate_limit=6G SCOPE=BOTH;

⚠️ Important DBA Checks

Before increasing PGA:

Verify server RAM availability
Identify runaway queries
Check parallel queries
Check large sorting operations

Example check:


SELECT *
FROM v$sql_workarea_active;

This shows active work areas consuming PGA.

📊 Root Cause (Real Production Case)

In one production system:

A reporting query ran with large ORDER BY and GROUP BY
Query processed millions of rows
Sort operation moved to disk
PGA usage increased rapidly

Application server: Oracle WebLogic Server

✅ Final Fix Implemented

1️⃣ Increased PGA


PGA_AGGREGATE_TARGET = 6G

2️⃣ Optimized query with indexes

3️⃣ Reduced unnecessary sorting

4️⃣ Limited parallel query usage

📘 Key Learning for Oracle DBAs

Always monitor:

v$pgastat
v$process
v$sql_workarea
v$sql_workarea_active

High PGA usage is usually caused by:

Large sorts
Hash joins
Parallel queries
Poorly optimized SQL

shashi

Monday, March 9, 2026

PGA Memory Issue