Oracle Memory Architecture: SGA, PGA, and Automatic Memory Management

One of the first things that trips up anyone learning Oracle coming from other databases is the memory model. It's not complicated once it clicks, but the terminology — SGA, PGA, ASMM, AMM — can feel like alphabet soup before you understand what each piece does and why it exists. This is my attempt to write the guide I wished I had when I started studying for this.

The Two Memory Areas: SGA and PGA

Oracle splits instance memory into two fundamentally different regions.

The System Global Area (SGA) is shared memory — every connected session and background process reads from and writes to it. It holds the database buffer cache (blocks read from datafiles), the shared pool (parsed SQL, PL/SQL code, data dictionary cache), the redo log buffer, and a few other components. Because it's shared, what one session puts into the buffer cache benefits every other session.

The Program Global Area (PGA) is private memory — each server process gets its own PGA and no other process can access it. Sort operations, hash joins, bitmap operations, and session-specific state all live here. The more memory you give to PGA, the more work Oracle can do in memory rather than spilling to temp tablespace on disk.

Understanding this split matters because the tuning levers for each are completely different.

How Memory Management Has Evolved

This is where the version history becomes important, and it's worth understanding the progression rather than jumping straight to the modern approach.

Manual Tuning (Pre-10g)

Before Oracle 10g, DBAs tuned every SGA component by hand. You'd set individual parameters like DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, LOG_BUFFER, and so on. If your shared pool ran hot and your buffer cache had headroom, you couldn't automatically rebalance — you'd adjust parameters and either bounce the instance or use ALTER SYSTEM for the dynamic ones. Getting this right required ongoing attention and a good understanding of your workload.

ASMM — Automatic Shared Memory Management (10g+)

Oracle 10g introduced Automatic Shared Memory Management (ASMM), which automates the balancing of memory within the SGA. Instead of setting each SGA component individually, you set:

• SGA_TARGET — the total SGA size Oracle should use
• SGA_MAX_SIZE — the maximum the SGA can grow to

Oracle then automatically redistributes memory between the buffer cache, shared pool, large pool, and Java pool based on workload demand. You can still set individual component sizes (like SHARED_POOL_SIZE), but under ASMM these act as minimum values — Oracle won't shrink that component below what you've set, but it can grow it beyond that if needed.

PGA was already semi-automatic by this point via PGA_AGGREGATE_TARGET, introduced in 9i, which set a target for total PGA across all sessions.

AMM — Automatic Memory Management (11g+)

Oracle 11g took this further with Automatic Memory Management (AMM), which manages the combined SGA and PGA pool together. The parameters are:

• MEMORY_TARGET — the total memory Oracle should use across SGA and PGA
• MEMORY_MAX_TARGET — the ceiling Oracle can never exceed

With AMM enabled, Oracle monitors both SGA and PGA demand and shifts memory between them dynamically. If a large batch job suddenly needs PGA for sort operations, Oracle can temporarily shrink the SGA buffer cache to accommodate it, then rebalance when the job finishes.

A key thing to understand: ASMM and AMM are mutually exclusive. If MEMORY_TARGET is set to a non-zero value, AMM is in control. If MEMORY_TARGET is 0 and SGA_TARGET is non-zero, ASMM is in control.

Enabling ASMM

-- Set the maximum SGA size (static, requires restart to change)
ALTER SYSTEM SET SGA_MAX_SIZE = 2G SCOPE=SPFILE;

-- Enable ASMM by setting SGA_TARGET
ALTER SYSTEM SET SGA_TARGET = 1500M SCOPE=BOTH;

-- Set PGA target separately
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 512M SCOPE=BOTH;

Enabling AMM

-- Set the memory ceiling (static)
ALTER SYSTEM SET MEMORY_MAX_TARGET = 4G SCOPE=SPFILE;

-- Set the memory target (can be adjusted dynamically within MEMORY_MAX_TARGET)
ALTER SYSTEM SET MEMORY_TARGET = 3G SCOPE=BOTH;

Note for RDS users: AWS RDS for Oracle sets MEMORY_TARGET to 75% of the DB instance class memory by default (MEMORY_TARGET = {DBInstanceClassMemory*3/4}). You can adjust it within bounds, but MEMORY_MAX_TARGET is fixed by the instance class.

SGA Component Sizes as Minimums Under ASMM

When ASMM is active, any component size you set explicitly becomes a floor, not a fixed allocation. Oracle can grow components above your specified values, but it will not shrink them below. This is useful for protecting critical memory areas:

-- Protect the shared pool from being shrunk below 512M
ALTER SYSTEM SET SHARED_POOL_SIZE = 512M;

-- Protect the buffer cache floor
ALTER SYSTEM SET DB_CACHE_SIZE = 1G;

The components Oracle auto-tunes under ASMM:

• DB_CACHE_SIZE
• SHARED_POOL_SIZE
• LARGE_POOL_SIZE
• JAVA_POOL_SIZE
• STREAMS_POOL_SIZE

Using the Memory Advisors

Oracle provides advisory views that help you understand whether your current memory settings are optimal — before you make a change. These are among the most practical tools available to a DBA doing performance analysis.

AMM Advisor: V$MEMORY_TARGET_ADVICE

When using AMM, query this view to see how changing MEMORY_TARGET would affect database time:

SELECT memory_size,
       memory_size_factor,
       estd_db_time,
       estd_db_time_factor
FROM   v$memory_target_advice
ORDER BY memory_size;

ESTD_DB_TIME_FACTOR tells you the estimated change in DB time relative to your current setting. A value of 1.0 is your baseline. Values below 1.0 mean you'd improve performance; values above 1.0 mean you'd regress. When the factor stops improving significantly as you increase memory size, you've found your optimal point — adding more memory beyond that won't help much.

ASMM Advisor: V$SGA_TARGET_ADVICE

When using ASMM, query this view to assess the impact of changing SGA_TARGET:

SELECT sga_size,
       sga_size_factor,
       estd_db_time,
       estd_db_time_factor
FROM   v$sga_target_advice
ORDER BY sga_size;

The logic is the same: SGA_SIZE_FACTOR of 1.0 is your current size. If you see diminishing returns past a certain SGA size — say, performance barely improves beyond 1000MB — that's your signal that the SGA is large enough and further memory is better spent on PGA or elsewhere.

PGA Advisor: V$PGA_TARGET_ADVICE

SELECT pga_target_for_estimate,
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count
FROM   v$pga_target_advice
ORDER BY pga_target_for_estimate;

The key column here is ESTD_PGA_CACHE_HIT_PERCENTAGE — the percentage of memory requests served from PGA rather than spilling to disk. The target zone is 75–100%. Below 75%, your sessions are frequently spilling to temp and you'll see performance impact on sort-heavy and join-heavy workloads.

ESTD_OVERALLOC_COUNT is also worth watching — a non-zero value means Oracle estimates it would over-allocate PGA at that target size, which causes memory pressure.

Important caveat: A cache hit percentage below 75% is a signal, not a diagnosis. ADDM may pick this up and recommend adjustments automatically. Don't tune in isolation — check ADDM findings alongside the advisor data.

Sizing Best Practices

A few things I've found consistently useful:

For 11g and above, prefer AMM unless you have a reason not to. Letting Oracle manage the combined SGA+PGA pool is almost always more efficient than splitting the job manually, especially on workloads that vary during the day.

Factor in Java if your application uses it. The default JAVA_POOL_SIZE is modest. If your application is Java-heavy or uses Oracle JVM, investigate whether it's sufficient before a production incident teaches you.

Estimate PGA based on peak concurrent sessions. Each connected session requires PGA for its private state. At peak load, if you have 200 active sessions each using 5MB of PGA for sort operations, that's 1GB of PGA demand before you even account for larger operations. Use V$SESSTAT and V$SQL_WORKAREA to understand your actual session-level PGA consumption.

Don't set component minimums too aggressively under ASMM. If you lock too much memory into minimums, Oracle loses the flexibility that makes ASMM valuable. Set floors only for components you know genuinely need protection.

Troubleshooting: ORA-04031

If you work with Oracle long enough, you will see this error:

ORA-04031: unable to allocate N bytes of shared memory
("shared pool","...","...")

It means Oracle couldn't allocate memory from the shared pool. The causes and fixes, in order of likelihood:

Shared pool is simply too small. Increase SHARED_POOL_SIZE (or MEMORY_TARGET/SGA_TARGET if using automatic management) to give the shared pool more room.

Shared pool fragmentation. Even if the total free memory in the shared pool looks adequate, it may be fragmented into chunks too small to satisfy a large allocation. The short-term fix is:

ALTER SYSTEM FLUSH SHARED POOL;

This clears unpinned objects and defragments the pool. Use with caution on production systems — it will cause a temporary performance hit as SQL gets re-parsed. It's a band-aid, not a solution.

Too many pinned packages. If you've used DBMS_SHARED_POOL.KEEP to pin packages, those chunks are never aged out. If you've pinned aggressively, there may not be enough room for new parse requests. Review what's pinned:

SELECT owner, name, type, kept
FROM   v$db_object_cache
WHERE  kept = 'YES';

The real fix is almost always to add memory. For 11g and above using AMM, increase MEMORY_MAX_TARGET and MEMORY_TARGET. For ASMM, increase SGA_TARGET and SHARED_POOL_SIZE. Also consider increasing SHARED_POOL_RESERVED_SIZE (default is 5% of shared pool), which reserves a chunk of shared pool for large contiguous allocations and is specifically designed to reduce ORA-04031 from fragmentation.

Summary

Oracle's memory management has evolved significantly from the manual era to AMM, and understanding the progression helps you reason about what each parameter actually does rather than cargo-culting values from a tuning guide. The advisors — V$MEMORY_TARGET_ADVICE, V$SGA_TARGET_ADVICE, and V$PGA_TARGET_ADVICE — give you real data to make those decisions rather than guessing. And when ORA-04031 shows up, the diagnosis follows a clear path: check size, check fragmentation, check pinned objects, then add RAM.

More Oracle internals coming as I work through them. If anything here is off or you've run into a different root cause for ORA-04031, drop it in the comments.