ORA-04030 Out of Process Memory - PGA Memory Management

ORA-04030: Out of Process Memory When Trying to Allocate

Error Overview

Error Text: ORA-04030: out of process memory when trying to allocate string bytes (string,string)

This error occurs when an Oracle server process cannot allocate enough memory in its Process Global Area (PGA). Unlike ORA-04031 which affects shared memory, ORA-04030 is specific to individual process memory and can cause sessions to fail.

Understanding PGA Memory

PGA Components

Process Global Area (PGA)
├── Stack Space (procedure calls, variables)
├── Session Memory (session-specific data)
├── SQL Work Areas
│   ├── Sort Operations
│   ├── Hash Joins
│   ├── Bitmap Operations
│   └── Group By Operations
└── Cursor State Areas

Memory Allocation Areas

• Stack space - Procedure calls and variables
• Sort area - ORDER BY, CREATE INDEX operations
• Hash area - Hash joins
• Bitmap area - Bitmap index operations
• UGA - User Global Area (session data)

Root Causes

1. Undersized PGA

• PGA_AGGREGATE_TARGET too small
• Large sort/hash operations
• Memory-intensive queries

2. Memory-Intensive Operations

• Large sort operations without indexes
• Hash joins on large datasets
• Parallel query operations
• Bulk operations (CTAS, large inserts)

3. Memory Leaks

• Unclosed cursors
• PL/SQL collections growing unbounded
• Recursive SQL consuming memory

4. System-Level Issues

• OS memory limits (ulimit)
• Swapping and virtual memory pressure
• Multiple processes competing for memory

Diagnostic Queries

Check PGA Configuration

-- Current PGA settings
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER workarea_size_policy;
SHOW PARAMETER sort_area_size;
SHOW PARAMETER hash_area_size;

-- PGA memory statistics
SELECT
  name,
  value/1024/1024 as mb,
  unit
FROM v$pgastat
WHERE name IN (
  'aggregate PGA target parameter',
  'total PGA allocated',
  'total PGA used by SQL workareas',
  'cache hit percentage'
)
ORDER BY name;

-- Process memory usage
SELECT
  s.sid, s.serial#, s.username, 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,
  ROUND(p.pga_max_mem/1024/1024,2) as pga_max_mb
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND s.username IS NOT NULL
ORDER BY p.pga_alloc_mem DESC;

Identify Memory-Intensive Operations

-- Active SQL workareas
SELECT
  sql_id, operation_type, operation_id,
  policy, estimated_optimal_size/1024/1024 est_opt_mb,
  estimated_onepass_size/1024/1024 est_onepass_mb,
  actual_mem_used/1024/1024 actual_mb,
  max_mem_used/1024/1024 max_mb,
  number_passes, active_time
FROM v$sql_workarea_active
WHERE actual_mem_used > 10485760  -- > 10MB
ORDER BY actual_mem_used DESC;

-- Historical workarea execution
SELECT
  sql_id, operation_type,
  ROUND(AVG(optimal_executions)) opt_exec,
  ROUND(AVG(onepass_executions)) onepass_exec,
  ROUND(AVG(multipasses_executions)) multipass_exec,
  ROUND(MAX(max_tempseg_size)/1024/1024) max_temp_mb
FROM v$sql_workarea_histogram
WHERE total_executions > 100
GROUP BY sql_id, operation_type
HAVING MAX(max_tempseg_size) > 104857600  -- > 100MB
ORDER BY max_temp_mb DESC;

-- PGA memory advice
SELECT pga_target_for_estimate/1024/1024 target_mb,
       pga_target_factor,
       estd_extra_bytes_rw/1024/1024 estd_extra_mb,
       estd_pga_cache_hit_percentage,
       estd_overallocation_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

Find Problematic SQL

-- SQL statements using most PGA memory
SELECT
  s.sql_id, s.sql_text,
  s.sorts, s.disk_reads, s.executions,
  ROUND(s.rows_processed/s.executions) avg_rows,
  ROUND(w.avg_workarea_mem/1024/1024,2) avg_workarea_mb,
  ROUND(w.max_workarea_mem/1024/1024,2) max_workarea_mb
FROM v$sqlarea s,
     (SELECT sql_id,
             AVG(estimated_optimal_size) avg_workarea_mem,
             MAX(estimated_optimal_size) max_workarea_mem
      FROM v$sql_workarea
      GROUP BY sql_id) w
WHERE s.sql_id = w.sql_id
  AND w.max_workarea_mem > 52428800  -- > 50MB
ORDER BY w.max_workarea_mem DESC;

-- Sessions with high PGA usage
SELECT
  s.sid, s.serial#, s.username, s.program, s.sql_id,
  ROUND(p.pga_used_mem/1024/1024,2) pga_used_mb,
  s.status, s.last_call_et
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND p.pga_used_mem > 104857600  -- > 100MB
ORDER BY p.pga_used_mem DESC;

Immediate Solutions

1. Increase PGA Memory

-- Increase PGA target
ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=BOTH;

-- For AMM (Automatic Memory Management)
ALTER SYSTEM SET memory_target = 16G SCOPE=BOTH;
-- Oracle will auto-allocate between SGA and PGA

-- Check if change took effect
SELECT value/1024/1024/1024 pga_gb
FROM v$parameter
WHERE name = 'pga_aggregate_target';

2. Kill Memory-Intensive Sessions

-- Find sessions using excessive memory
SELECT s.sid, s.serial#, s.username, s.program, s.sql_id,
       ROUND(p.pga_alloc_mem/1024/1024,2) pga_mb
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND p.pga_alloc_mem > 1073741824  -- > 1GB
ORDER BY p.pga_alloc_mem DESC;

-- Kill problematic session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

3. Optimize Immediate Problem Queries

-- For sort operations
ALTER SESSION SET sort_area_size = 1048576000;  -- 1GB
ALTER SESSION SET workarea_size_policy = MANUAL;

-- For hash joins
ALTER SESSION SET hash_area_size = 1048576000;  -- 1GB

-- Use parallel query with memory limits
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION SET parallel_max_servers = 4;

Long-Term Solutions

1. Optimize Workarea Policy

-- Enable automatic workarea sizing
ALTER SYSTEM SET workarea_size_policy = AUTO SCOPE=BOTH;

-- Set appropriate PGA target (typically 20-80% of total RAM)
ALTER SYSTEM SET pga_aggregate_target = 10G SCOPE=BOTH;

-- Limit individual process memory (12c+)
ALTER SYSTEM SET pga_aggregate_limit = 12G SCOPE=BOTH;

2. SQL Optimization

-- Example: Optimize large sort operations
-- BAD: Full table scan with sort
SELECT * FROM large_table ORDER BY date_column;

-- GOOD: Use index to avoid sort
CREATE INDEX idx_large_table_date ON large_table(date_column);
SELECT * FROM large_table ORDER BY date_column;

-- BAD: Large hash join
SELECT /*+ USE_HASH(a,b) */ *
FROM large_table1 a, large_table2 b
WHERE a.id = b.id;

-- GOOD: Use nested loop with indexes
CREATE INDEX idx_table2_id ON large_table2(id);
SELECT /*+ USE_NL(a,b) */ *
FROM large_table1 a, large_table2 b
WHERE a.id = b.id;

-- For unavoidable large operations, use parallel processing
SELECT /*+ PARALLEL(4) */ *
FROM large_table
ORDER BY expensive_function(column1);

3. PL/SQL Memory Management

-- BAD: Unbounded collection growth
DECLARE
  TYPE t_records IS TABLE OF large_table%ROWTYPE;
  l_records t_records := t_records();
BEGIN
  FOR rec IN (SELECT * FROM large_table) LOOP
    l_records.EXTEND;
    l_records(l_records.LAST) := rec;  -- Memory keeps growing
  END LOOP;
END;

-- GOOD: Process in batches
DECLARE
  TYPE t_records IS TABLE OF large_table%ROWTYPE;
  l_records t_records;
  CURSOR c_data IS SELECT * FROM large_table;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data BULK COLLECT INTO l_records LIMIT 1000;
    EXIT WHEN l_records.COUNT = 0;

    -- Process batch
    FOR i IN 1..l_records.COUNT LOOP
      process_record(l_records(i));
    END LOOP;

    -- Free memory
    l_records.DELETE;

  END LOOP;
  CLOSE c_data;
END;

Memory Sizing Guidelines

Calculate Optimal PGA Size

-- Estimate PGA requirements
WITH pga_analysis AS (
  SELECT
    COUNT(*) concurrent_sessions,
    MAX(pga_alloc_mem) max_session_pga,
    AVG(pga_alloc_mem) avg_session_pga,
    SUM(pga_alloc_mem) total_pga_used
  FROM v$process p, v$session s
  WHERE p.addr = s.paddr
    AND s.username IS NOT NULL
)
SELECT
  concurrent_sessions,
  ROUND(max_session_pga/1024/1024,2) max_session_mb,
  ROUND(avg_session_pga/1024/1024,2) avg_session_mb,
  ROUND(total_pga_used/1024/1024,2) current_total_mb,
  ROUND(total_pga_used/1024/1024 * 1.5,2) recommended_min_mb,
  ROUND(total_pga_used/1024/1024 * 2,2) recommended_safe_mb
FROM pga_analysis;

-- Workarea histogram analysis
SELECT
  low_optimal_size/1024 low_kb,
  high_optimal_size/1024 high_kb,
  optimal_executions,
  onepass_executions,
  multipasses_executions,
  total_executions
FROM v$sql_workarea_histogram
WHERE total_executions > 0
ORDER BY low_optimal_size;

Memory Allocation Formula

PGA_AGGREGATE_TARGET = (Expected Concurrent Sessions × Average PGA per Session × 1.2) + Workarea Memory

Where:
- Average PGA per Session: 10-50MB (OLTP) or 100-500MB (DW/Analytics)
- Workarea Memory: 20-40% of total PGA target
- Safety Factor: 1.2 (20% overhead)

Monitoring and Prevention

Create PGA Monitoring

-- Table for PGA alerts
CREATE TABLE dba_pga_alerts (
  alert_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  alert_type VARCHAR2(30),
  sid NUMBER,
  serial# NUMBER,
  username VARCHAR2(30),
  pga_used_mb NUMBER,
  sql_id VARCHAR2(13),
  details VARCHAR2(500)
);

-- Monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_pga_usage AS
  v_pga_target NUMBER;
  v_pga_used NUMBER;
  v_utilization_pct NUMBER;
BEGIN
  -- Get PGA utilization
  SELECT
    (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_target'),
    (SELECT value FROM v$pgastat WHERE name = 'total PGA allocated')
  INTO v_pga_target, v_pga_used
  FROM dual;

  v_utilization_pct := (v_pga_used / v_pga_target) * 100;

  -- Alert if utilization > 85%
  IF v_utilization_pct > 85 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: PGA utilization at ' ||
                        ROUND(v_utilization_pct,2) || '%');
  END IF;

  -- Check for sessions using excessive memory
  FOR rec IN (
    SELECT s.sid, s.serial#, s.username, p.pga_alloc_mem,
           s.sql_id
    FROM v$session s, v$process p
    WHERE s.paddr = p.addr
      AND p.pga_alloc_mem > v_pga_target * 0.1  -- > 10% of target
  ) LOOP
    INSERT INTO dba_pga_alerts (
      alert_type, sid, serial#, username,
      pga_used_mb, sql_id, details
    ) VALUES (
      'HIGH_SESSION_PGA', rec.sid, rec.serial#, rec.username,
      ROUND(rec.pga_alloc_mem/1024/1024,2), rec.sql_id,
      'Session using ' || ROUND(rec.pga_alloc_mem/1024/1024,2) || 'MB'
    );
  END LOOP;

  COMMIT;
END;
/

-- Schedule monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_PGA_USAGE',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_pga_usage',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
    enabled => TRUE
  );
END;
/

Automated Tuning

-- Auto-tune PGA based on workload
CREATE OR REPLACE PROCEDURE auto_tune_pga AS
  v_cache_hit_pct NUMBER;
  v_current_target NUMBER;
  v_recommended_target NUMBER;
BEGIN
  -- Get current cache hit percentage
  SELECT value INTO v_cache_hit_pct
  FROM v$pgastat
  WHERE name = 'cache hit percentage';

  SELECT value INTO v_current_target
  FROM v$parameter
  WHERE name = 'pga_aggregate_target';

  -- If cache hit < 80%, consider increasing PGA
  IF v_cache_hit_pct < 80 THEN
    v_recommended_target := v_current_target * 1.5;

    DBMS_OUTPUT.PUT_LINE('Current PGA cache hit: ' || v_cache_hit_pct || '%');
    DBMS_OUTPUT.PUT_LINE('Recommend increasing PGA to: ' ||
                        ROUND(v_recommended_target/1024/1024/1024,2) || 'GB');

    -- Optionally auto-adjust (be careful!)
    -- EXECUTE IMMEDIATE 'ALTER SYSTEM SET pga_aggregate_target = ' ||
    --                   v_recommended_target || ' SCOPE=BOTH';
  END IF;
END;
/

OS-Level Configuration

Check System Limits

# Check current limits for oracle user
su - oracle
ulimit -a

# Memory limits
ulimit -m  # Max memory size
ulimit -v  # Virtual memory
ulimit -d  # Data segment size

# If limits too low, update /etc/security/limits.conf
oracle soft memlock 3145728
oracle hard memlock 3145728
oracle soft as unlimited
oracle hard as unlimited

Memory Overcommit (Linux)

# Check overcommit settings
cat /proc/sys/vm/overcommit_memory
cat /proc/sys/vm/overcommit_ratio

# Recommended settings for Oracle
echo 2 > /proc/sys/vm/overcommit_memory
echo 80 > /proc/sys/vm/overcommit_ratio

# Make permanent in /etc/sysctl.conf
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

Related Errors

• ORA-04031 - Unable to allocate shared memory
• ORA-01652 - Unable to extend temp segment
• ORA-27102 - Out of memory
• ORA-12500 - TNS:listener failed to start

Emergency Response

Quick Diagnostic Script

-- Emergency PGA analysis
SET PAGESIZE 100
SET LINESIZE 200

PROMPT === PGA Emergency Analysis ===

PROMPT Current PGA Configuration:
SELECT name, value, unit FROM v$parameter
WHERE name LIKE '%pga%' OR name LIKE '%workarea%';

PROMPT PGA Statistics:
SELECT name, ROUND(value/1024/1024,2) mb
FROM v$pgastat
WHERE name IN ('total PGA allocated','cache hit percentage');

PROMPT Top Memory Users:
SELECT s.sid, s.serial#, s.username, s.sql_id,
       ROUND(p.pga_alloc_mem/1024/1024,2) pga_mb
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND p.pga_alloc_mem > 104857600
ORDER BY p.pga_alloc_mem DESC;

PROMPT Memory-Intensive Operations:
SELECT sql_id, operation_type,
       ROUND(actual_mem_used/1024/1024,2) mem_mb,
       number_passes
FROM v$sql_workarea_active
WHERE actual_mem_used > 52428800
ORDER BY actual_mem_used DESC;

Emergency Actions

1. Increase PGA immediately

   ALTER SYSTEM SET pga_aggregate_target = 16G SCOPE=MEMORY;

2. Kill high-memory sessions

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

3. Disable parallel operations temporarily

   ALTER SYSTEM SET parallel_max_servers = 0 SCOPE=MEMORY;

4. Monitor system memory

   free -m
   vmstat 5