Oracle PGA memory pressure: over-allocation, temp spills, and PGA_AGGREGATE_TARGET

Program Global Area (PGA) is the private memory each dedicated server process uses for sorting, hashing, bitmap operations, and session state. It is allocated outside the SGA, one chunk per process, and managed as an aggregate pool with a soft target (PGA_AGGREGATE_TARGET) and, from Oracle 12c onward, a hard ceiling (PGA_AGGREGATE_LIMIT). PGA pressure has two opposite failure signatures, and the operator’s job is to tell them apart quickly.

The first signature is “too little PGA”: work areas cannot fit, the optimizer still picks hash joins and sorts, and Oracle silently spills those work areas to temp tablespace. The wait events direct path read temp and direct path write temp show up, temp fills faster, and query latency rises without a clean error. The second signature is “too much PGA”: total allocation grows past the soft target toward the hard limit, and either sessions hit ORA-04036 or, if PGA plus SGA exceeds the box, the Linux OOM killer targets Oracle server processes.

Both directions can coexist on a mixed-workload instance. Raising the target fixes spills but raises OOM risk; lowering it fixes OOM risk but increases spills.

What this means

PGA_AGGREGATE_TARGET is a soft target. Oracle tries to keep the sum of all PGA below it, but individual work areas can exceed the per-session optimal when the workload demands it. When that happens, the over allocation count statistic in V$PGASTAT increments. A non-zero and growing over allocation count means the target is too small for the actual workload. It is not, by itself, an incident. Some over-allocation is expected on analytics. It becomes a problem only when it correlates with declining cache hit percentage, rising direct path read temp waits, or ORA-04036.

PGA_AGGREGATE_LIMIT (12c+) is a hard ceiling. When the instance hits it, Oracle interrupts calls or sessions with the most untunable PGA memory to get back under the limit; the affected call can fail with ORA-04036. On small VMs the default limit computation can land close to physical RAM, so ORA-04036 sometimes appears shortly after upgrade without any workload change. Check dmesg and the alert log together: OOM killer entries mean the OS killed Oracle; ORA-04036 means Oracle killed its own session before the OS got involved.

The cache hit percentage in V$PGASTAT is computed as (bytes processed * 100) / (bytes processed + extra bytes read/written for spills). Sustained below 80% means significant work is being done in temp that could fit in PGA. The target is a workload-dependent tuning knob, not an absolute. OLTP instances may run at 100% with a small target; mixed or analytical instances need a much larger target to keep the same hit percentage.

flowchart TD
    A[Sort or hash join
needs work area] --> B{Fits in PGA?}
    B -- Yes --> C[Optimal execution
in memory]
    B -- No --> D[Spill to temp
direct path read/write temp]
    D --> E[Cache hit pct drops
over-allocation grows]
    E --> F{Total PGA near
PGA_AGGREGATE_LIMIT?}
    F -- Yes --> G[ORA-04036
call aborted or session killed]
    F -- No, but SGA+PGA
exceeds physical RAM --> H[Linux OOM killer
targets Oracle PID]
    H --> I[Random session death
ORA-27300 / ORA-03113]

Common causes

Quick checks

Run these read-only. None change instance state.

# Confirm whether the OOM killer has been touching Oracle processes
dmesg -T | grep -iE "out of memory|oom|killed process" | tail -30

-- Core PGA health from V$PGASTAT
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN (
  'aggregate PGA target parameter',
  'aggregate PGA auto target',
  'total PGA inuse',
  'total PGA allocated',
  'maximum PGA allocated',
  'total freeable PGA memory',
  'over allocation count',
  'cache hit percentage'
);

-- Top PGA consumers right now (12c+ FETCH FIRST; use ROWNUM <= 20 on 11g)
SELECT p.SPID, p.PGA_USED_MEM/1048576  AS pga_used_mb,
       p.PGA_ALLOC_MEM/1048576 AS pga_alloc_mb,
       p.PGA_MAX_MEM/1048576   AS pga_max_mb,
       s.SID, s.USERNAME, s.SQL_ID
FROM V$PROCESS p
LEFT JOIN V$SESSION s ON p.ADDR = s.PADDR
ORDER BY p.PGA_ALLOC_MEM DESC
FETCH FIRST 20 ROWS ONLY;

-- Temp spill waits (PGA undersize indicator)
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO/NULLIF(TOTAL_WAITS,0)/1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT IN ('direct path read temp', 'direct path write temp');

-- Confirm memory management mode
SELECT NAME, VALUE, ISDEFAULT
FROM V$PARAMETER
WHERE NAME IN ('pga_aggregate_target',
               'pga_aggregate_limit',
               'memory_target',
               'memory_max_target',
               'sga_target',
               'workarea_size_policy');

How to diagnose it

1. Confirm direction. Read V$PGASTAT. If over allocation count is growing and cache hit percentage is below 80%, the problem is undersized PGA. If total PGA allocated is hovering near PGA_AGGREGATE_LIMIT and ORA-04036 appears in the alert log, the problem is oversized sessions relative to the limit. If dmesg shows OOM kills, the problem is total Oracle memory versus physical RAM, not PGA tuning alone.
2. Find the heavy consumers. Sort V$PROCESS by PGA_ALLOC_MEM. A normal OLTP session is in the tens of MB. A hash join against a large build table or a large sort can be GB-scale. If one or two SPIDs dominate, the fix is at the SQL or workload level, not the target.
3. Correlate with workload. Match the pressure window to the batch schedule, the stats gathering job, or the reporting window. PGA pressure that only appears during DBMS_STATS or during a known ETL is a workload-isolation problem first and a tuning problem second.
4. Check the memory management mode. If MEMORY_TARGET is set, AMM is in use. On Linux that means the SGA lives in /dev/shm instead of hugepages, page table overhead grows with process count, and /dev/shm can fill independently of PGA. ASMM (SGA_TARGET plus PGA_AGGREGATE_TARGET) is the recommended production configuration on Linux.
5. Check the advice view. V$PGA_TARGET_ADVICE predicts cache hit percentage and over allocation count at a range of target sizes. Oracle’s tuning guide enables generation by setting PGA_AGGREGATE_TARGET with automatic PGA memory management and STATISTICS_LEVEL to TYPICAL (the default) or ALL; BASIC disables it. Confirm any Oracle management-pack entitlement separately before relying on advisory data.
6. Rule out undo cross-talk. Undo pressure and PGA pressure both surface during long-running reports, but the fixes are different. If the failing queries are reads that error with ORA-01555, the root cause is undo, not PGA. See the ORA-01555 snapshot too old guide for that path.

Metrics and signals to monitor

Fixes

PGA_AGGREGATE_TARGET is undersized

Use V$PGA_TARGET_ADVICE to pick a new target that lifts cache hit percentage above 90% without forcing total PGA allocated against the hard limit. Apply the change at runtime:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = <value>M SCOPE=BOTH;

This is dynamic and reversible. Watch over allocation count (cumulative; track the rate, not the absolute) and cache hit percentage for the next maintenance window. If spill waits drop and temp utilization stabilizes, the change worked. If the rate of over-allocation does not change, the workload has a hard lower bound on PGA that target tuning alone cannot fix.

A single session or SQL is consuming too much PGA

Tuning the target will not help here. Identify the SQL_ID from V$PROCESS joined to V$SESSION, then look at the execution plan. Common patterns: a hash join with a much larger build input than the optimizer estimated, a sort on an unindexed column set, or a PL/SQL collection growing without bound. Fix the plan (statistics, SQL profile, SQL plan baseline) or partition the workload. If the consumer is a runaway batch job, killing the session is a valid short-term intervention; it will not change recurrence.

-- WARNING: this terminates the session immediately.
-- Roll back uncommitted work and may cascade to dependent sessions.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

PGA_AGGREGATE_LIMIT is too close to physical RAM

Lower the limit if the OS OOM killer is firing, or raise it if ORA-04036 is firing without OS pressure. Both are dynamic:

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = <value>M SCOPE=BOTH;

When MEMORY_TARGET is not set, the documented default is normally 200% of PGA_AGGREGATE_TARGET; if that target is explicitly 0, it is 90% of physical memory minus SGA. In all cases the default is at least 2 GB and at least 3 MB × PROCESSES (5 MB × PROCESSES for Oracle RAC). Do not set PGA_AGGREGATE_LIMIT below its computed default: Oracle documents that instance startup will fail. A value of 0 disables the limit. For a PDB, the limit must be at least twice that PDB’s PGA_AGGREGATE_TARGET; violating the range can return ORA-00093, whose meaning is “parameter outside valid range.” On small VMs, the 2 GB or PROCESSES-derived floor can land close to physical RAM and produce ORA-04036 shortly after upgrade. The alert log warning “pga_aggregate_limit value is too high for the amount of physical memory” does not block startup but signals the mismatch.

AMM is in use on Linux

Move to ASMM. This is not a runtime-only change: removing MEMORY_TARGET and adopting SGA_TARGET plus PGA_AGGREGATE_TARGET requires a planned restart, and you must provision hugepages for the SGA first. Verify with /proc/meminfo that HugePages_Total covers the SGA and HugePages_Free is non-zero. Without hugepages, each Oracle server process carries page table overhead that does not appear in V$PGASTAT or V$SGASTAT but does count against physical RAM. The OOM killer sees that overhead; Oracle does not. AMM also couples SGA and PGA in ways that defeat hugepages even when they are configured, which is the core reason it is not recommended for production on Linux.

Mixed OLTP and analytics

If OLTP is fine during the day and pressure appears only during batch or reporting windows, isolate the workloads. Options include an Active Data Guard standby for reads, a separate reporting instance, Resource Manager directives, or rescheduling the batch window. Tuning PGA_AGGREGATE_TARGET for the peak analytical workload over-provisions PGA for the OLTP trough and may bring ORA-04036 or OOM risk back during the wrong window.

Prevention

• Track over allocation count rate, not the absolute. It is cumulative since startup. The diagnostic signal is the slope, sampled at consistent workload windows.
• Track cache hit percentage against a workload-specific baseline. A mixed workload that lives at 85% may be normal; the same instance dropping from 95% to 75% after a stats gather is not.
• Watch V$PROCESS.PGA_MAX_MEM for monotonic growth. A session whose max PGA keeps climbing after the statement that needed it has finished is a leak, not a tuning problem.
• Size for physical RAM, not for PGA_AGGREGATE_TARGET in isolation. The real budget is physical_RAM - SGA - OS_overhead - other_processes. PGA lives inside that envelope.
• Use ASMM plus hugepages on Linux. AMM is workable on small systems but is not the production recommendation on Linux.
• Validate PGA_AGGREGATE_LIMIT after every upgrade. The limit was introduced in 12c and the default computation has changed across releases. What was fine on 12.1 may behave differently on 19c.

How Netdata helps

Netdata’s per-second collection turns PGA pressure from a guessed-at tuning problem into a correlation problem. The signals worth wiring together:

• V$PGASTAT rows (over allocation count, cache hit percentage, total PGA allocated, maximum PGA allocated) collected at per-second granularity, so a burst of over-allocation during a batch window shows up against the OLTP baseline.
• V$PROCESS.PGA_ALLOC_MEM per SPID, so the single runaway session appears as a spike alongside the aggregate trend rather than being hidden inside it.
• direct path read temp and direct path write temp wait time, so spills correlate with cache hit percentage drops and temp tablespace growth.
• OS memory, MemAvailable, hugepages utilization, and dmesg OOM entries on the same timeline, so ORA-04036 in the alert log lines up with OOM killer activity on the host.
• ML anomaly detection on total PGA allocated and cache hit percentage, which catches slow drift toward the limit before ORA-04036 fires.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00257: archiver error, connect internal only until freed
• ORA-01555: snapshot too old, rollback segment too small
• ORA-01652: unable to extend temp segment in tablespace