ORA-04036: PGA Memory Used by Instance Exceeds PGA_AGGREGATE_LIMIT - Fix Oracle Memory Error

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

Error Overview

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

This error occurs when the total Program Global Area (PGA) memory usage across all sessions in an Oracle instance exceeds the limit set by the PGA_AGGREGATE_LIMIT parameter. Introduced in Oracle 12c, this parameter acts as a hard limit to prevent memory exhaustion and system crashes.

Understanding PGA Memory Management

What is PGA?

Program Global Area (PGA) is private memory allocated to each Oracle server process, containing:

• Sort Areas: Memory for ORDER BY and GROUP BY operations
• Hash Areas: Memory for hash joins and other hash operations
• Bitmap Areas: Memory for bitmap index operations
• Session Memory: Variables, cursors, and other session-specific data

PGA_AGGREGATE_LIMIT Parameter

• Default Value: MAX(2GB, 200% of PGA_AGGREGATE_TARGET)
• Minimum Value: 10MB
• Maximum Value: 4096GB
• Scope: Instance-wide hard limit across all sessions

Memory Hierarchy

MEMORY_TARGET (if AMM enabled)
├── SGA_TARGET
└── PGA_AGGREGATE_TARGET (soft limit)
    └── PGA_AGGREGATE_LIMIT (hard limit)

Common Causes

1. Undersized PGA Configuration

• PGA_AGGREGATE_LIMIT set too low for workload
• PGA_AGGREGATE_TARGET insufficient for concurrent sessions
• Memory parameters not adjusted after hardware upgrades
• Default values inadequate for high-memory operations

2. Memory-Intensive Operations

• Large sort operations without proper indexing
• Hash joins on large datasets
• Parallel query operations with high degree of parallelism
• Bulk data loading operations
• Complex analytical queries

3. Session Management Issues

• Too many concurrent sessions
• Sessions not properly closed by applications
• Connection pooling misconfiguration
• Long-running sessions accumulating memory

4. Application Design Problems

• Inefficient SQL queries causing memory spikes
• Lack of proper query optimization
• Missing or inappropriate indexes
• Poor cursor management in applications

Diagnostic Steps

1. Check Current PGA Usage

-- Current PGA configuration and usage
SELECT name, value, unit, description
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit', 'memory_target');

-- Current PGA memory usage
SELECT * FROM v$pgastat
WHERE name IN ('total PGA allocated', 'maximum PGA allocated');

-- PGA usage by session
SELECT sid, serial#, username, program,
       pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$process p, v$session s
WHERE p.addr = s.paddr
ORDER BY pga_used_mem DESC;

2. Identify Memory-Intensive Sessions

-- Top PGA consumers
SELECT s.sid, s.serial#, s.username, s.program, s.machine,
       p.pga_used_mem/1024/1024 as pga_used_mb,
       p.pga_alloc_mem/1024/1024 as pga_alloc_mb,
       p.pga_max_mem/1024/1024 as pga_max_mb,
       s.status, s.sql_id
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.pga_used_mem > 100*1024*1024  -- Sessions using more than 100MB
ORDER BY p.pga_used_mem DESC;

3. Analyze SQL Workload

-- Memory-intensive SQL statements
SELECT sql_id, executions, avg_px_servers_executions,
       sorts_mem, sorts_disk, hash_mem, hash_disk,
       elapsed_time/1000000 as elapsed_sec,
       sql_text
FROM v$sql
WHERE sorts_mem > 1000 OR hash_mem > 1000
ORDER BY sorts_mem + hash_mem DESC;

4. Check PGA Advice

-- PGA sizing advice
SELECT pga_target_for_estimate/1024/1024 as target_mb,
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

Solutions

Solution 1: Increase PGA_AGGREGATE_LIMIT

-- Check current setting
SHOW PARAMETER pga_aggregate_limit;

-- Increase the limit (requires restart)
ALTER SYSTEM SET pga_aggregate_limit = 8G SCOPE = SPFILE;

-- For immediate effect (if AMM not used)
ALTER SYSTEM SET pga_aggregate_limit = 8G;

Solution 2: Optimize PGA_AGGREGATE_TARGET

-- Increase PGA target for better memory distribution
ALTER SYSTEM SET pga_aggregate_target = 4G;

-- Check if change is effective
SELECT * FROM v$pgastat WHERE name LIKE '%target%';

Solution 3: Kill Memory-Intensive Sessions

-- Identify and kill problematic sessions
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';'
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.pga_used_mem > 500*1024*1024  -- Sessions using more than 500MB
AND s.status = 'ACTIVE';

-- Execute the generated kill commands
ALTER SYSTEM KILL SESSION '123,45678';

Solution 4: Optimize Problematic Queries

-- Find SQL using excessive PGA memory
SELECT s.sql_id, s.sql_text, p.pga_used_mem/1024/1024 as pga_mb
FROM v$session s, v$process p, v$sql sq
WHERE s.paddr = p.addr
AND s.sql_id = sq.sql_id
AND p.pga_used_mem > 100*1024*1024
ORDER BY p.pga_used_mem DESC;

-- Create execution plans for optimization
EXPLAIN PLAN FOR <your_sql_here>;
SELECT * FROM TABLE(dbms_xplan.display);

Long-Term Solutions

1. Workload-Based Memory Sizing

-- Calculate optimal PGA settings based on workload
SELECT
  ROUND(MAX(total_pga)/1024/1024) as max_pga_mb,
  ROUND(AVG(total_pga)/1024/1024) as avg_pga_mb,
  COUNT(*) as sample_count
FROM (
  SELECT sum(pga_alloc_mem) as total_pga
  FROM v$process p, v$session s
  WHERE p.addr = s.paddr
  AND s.username IS NOT NULL
);

2. Implement Connection Pooling

// Example: Configure connection pool properly
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(50);  // Limit concurrent connections
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

3. Query Optimization Strategy

-- Add indexes to reduce sort memory usage
CREATE INDEX idx_large_table_date ON large_table(date_column);

-- Use partitioning for large tables
CREATE TABLE large_table_partitioned
PARTITION BY RANGE (date_column) (
  PARTITION p1 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p2 VALUES LESS THAN (DATE '2024-02-01')
);

Monitoring and Prevention

1. Automated Monitoring

-- Create monitoring view for PGA usage
CREATE OR REPLACE VIEW pga_usage_monitor AS
SELECT
  ROUND(current_pga.value/1024/1024) as current_pga_mb,
  ROUND(max_pga.value/1024/1024) as max_pga_mb,
  ROUND(target.value/1024/1024) as target_pga_mb,
  ROUND(limit_param.value/1024/1024) as limit_pga_mb,
  ROUND((current_pga.value/limit_param.value)*100,2) as pct_of_limit
FROM
  (SELECT value FROM v$pgastat WHERE name = 'total PGA allocated') current_pga,
  (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga,
  (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_target') target,
  (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') limit_param;

2. Alert Implementation

-- Create alert when PGA usage exceeds 80% of limit
BEGIN
  DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.METRIC_PGA_USAGE,
    warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
    warning_value => '80',
    critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
    critical_value => '90',
    observation_period => 5,
    consecutive_occurrences => 2
  );
END;
/

3. Regular Maintenance

#!/bin/bash
# Daily PGA monitoring script
sqlplus -s / as sysdba << EOF
SET PAGESIZE 0 FEEDBACK OFF
SELECT 'PGA_USAGE: ' ||
       ROUND((current_pga.value/limit_param.value)*100,2) || '%'
FROM
  (SELECT value FROM v\$pgastat WHERE name = 'total PGA allocated') current_pga,
  (SELECT value FROM v\$parameter WHERE name = 'pga_aggregate_limit') limit_param;
EOF

Oracle Version Considerations

Oracle 12c and Later

• PGA_AGGREGATE_LIMIT is available and recommended
• Automatic Memory Management (AMM) integration
• Enhanced PGA monitoring capabilities

Pre-12c Versions

• Use PGA_AGGREGATE_TARGET with careful monitoring
• Implement manual session management
• Regular monitoring of v$process and v$session_memory

Related Errors

• ORA-04030: Out of process memory
• ORA-04031: Unable to allocate shared memory
• ORA-01012: Not logged on (session killed due to memory)
• ORA-00020: Maximum number of processes exceeded

Quick Reference

Emergency Response Steps

1. Check PGA usage: Query v$pgastat for current usage
2. Identify top consumers: Find sessions using most PGA memory
3. Kill problematic sessions: Terminate sessions if necessary
4. Increase limit temporarily: Adjust PGA_AGGREGATE_LIMIT if possible

Key Monitoring Queries

-- Current PGA usage percentage
SELECT ROUND((pga_used.value/pga_limit.value)*100,2) as pga_usage_pct
FROM v$pgastat pga_used, v$parameter pga_limit
WHERE pga_used.name = 'total PGA allocated'
AND pga_limit.name = 'pga_aggregate_limit';

-- Top PGA consuming sessions
SELECT sid, pga_used_mem/1024/1024 as pga_mb
FROM v$process p, v$session s
WHERE p.addr = s.paddr
ORDER BY pga_used_mem DESC
FETCH FIRST 10 ROWS ONLY;