Sizing SGA and PGA Memory in Oracle Database

Memory sizing on Oracle is one of the few tuning problems where the database will tell you the answer if you ask it. The instance keeps running estimates of how it would perform at different SGA and PGA sizes, and it exposes them as advisory views. I lean on those instead of applying a fixed RAM percentage and hoping.

Two pools, two jobs

The SGA is shared memory. Every server process attached to the instance reads and writes it. Two components dominate.

• Buffer cache (db_cache_size) holds copies of data blocks so queries avoid physical reads.

• Shared pool (shared_pool_size) holds the library cache (parsed SQL and execution plans) plus the dictionary cache. Starve it and you get excess hard parsing and eventually ORA-04031.

The large pool, Java pool, streams pool, and redo log buffer make up the rest.

The PGA is private. Each dedicated server process gets its own slice and shares none of it. It holds sort areas, hash join work areas, bitmap merge areas, and session state. The work areas are what you actually tune. A sort or hash join that fits in its work area runs entirely in memory (optimal). When it overflows, it spills to temp: one pass if the area is close, multiple passes if it is far too small.

Rough division of labor. OLTP leans on the SGA through buffer cache hits and plan reuse. Reporting and batch lean on the PGA through large sorts and hash joins.

Choosing a management mode

AMM

memory_target turns on Automatic Memory Management. Oracle manages a single pool and shifts memory between SGA and PGA on its own, up to memory_max_target.

I avoid AMM on anything sizeable. On Linux it allocates the SGA out of /dev/shm, and it cannot coexist with HugePages. On a server with a large SGA, losing HugePages means a bloated page table and measurable CPU overhead walking it. AMM is fine for small or lab instances.

ASMM

Set sga_target and pga_aggregate_target separately and leave memory_target at 0. This is Automatic Shared Memory Management, and it is what I run in production.

Under sga_target, Oracle auto-tunes the buffer cache, shared pool, large pool, Java pool, and streams pool. sga_max_size is the static ceiling those can grow toward. A few components sit outside the auto-tuned set and are subtracted from the target: db_keep_cache_size, db_recycle_cache_size, any non-default block size caches, and log_buffer.

pga_aggregate_target sets a goal, and Oracle can run past it under concurrency. pga_aggregate_limit (12c) is the hard ceiling. When sessions breach it, Oracle terminates the calls holding the largest untunable PGA. Its default is the greatest of 2 GB, twice pga_aggregate_target, or 3 MB times the processes parameter.

Sizing the SGA

v$sga_target_advice projects DB time and physical reads across a range of SGA sizes, each expressed as a factor of the current one.

SELECT sga_size            AS sga_mb,
       sga_size_factor     AS factor,
       estd_db_time,
       estd_physical_reads
FROM   v$sga_target_advice
ORDER  BY sga_size;

Read it by factor. The row where sga_size_factor = 1 is where you sit now. Walk up the sizes and watch estd_db_time. While it keeps dropping by a meaningful amount, the instance is memory-starved and more SGA pays off. Where the curve flattens, you have reached diminishing returns, and there is no reason to hand the SGA more RAM past that knee.

For the two heavy consumers, drill into their own advisories.

SELECT size_for_estimate          AS cache_mb,
       size_factor,
       estd_physical_read_factor,
       estd_physical_reads
FROM   v$db_cache_advice
WHERE  name = 'DEFAULT'
AND    block_size = (SELECT value FROM v$parameter
                     WHERE name = 'db_block_size')
ORDER  BY size_for_estimate;

v$db_cache_advice gives the same shape for the buffer cache alone, tracking estd_physical_read_factor against size_factor. v$shared_pool_advice reports estd_lc_time_saved, the parse time saved at each shared pool size, which is the number to watch when hard parsing is the problem.

Under ASMM you do not have to hard-set these components, but you can set floors. A value assigned to db_cache_size or shared_pool_size becomes a minimum the auto-tuner will not drop below.

-- Minimum floors under the auto-tuned components (ASMM)
ALTER SYSTEM SET db_cache_size    = 12G SCOPE=BOTH;
ALTER SYSTEM SET shared_pool_size = 3G  SCOPE=BOTH;

I use floors when a component gets starved in a specific window, like a shared pool that shrinks overnight and then hard-parses all morning until it grows back.

Sizing the PGA

Two views do the work here. v$pga_target_advice predicts behavior at different targets, and v$pgastat reports what is happening right now.

SELECT ROUND(pga_target_for_estimate/1024/1024/1024, 1) AS pga_gb,
       pga_target_factor                                AS factor,
       estd_pga_cache_hit_percentage                    AS cache_hit_pct,
       estd_overalloc_count
FROM   v$pga_target_advice
ORDER  BY pga_target_for_estimate;

I read two columns. estd_overalloc_count has to reach 0 at the target I choose. Any nonzero value means that target cannot satisfy the minimum work area demand and Oracle will be forced to allocate beyond it. Then I find where estd_pga_cache_hit_percentage plateaus. Once the next few GB buys less than a point or two of cache hit, I stop.

Confirm the prediction against live counters.

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',
                'over allocation count',
                'cache hit percentage');

The rows that matter:

• over allocation count should stay at or near zero since startup. A climbing value is the plainest signal the target is too small.

• cache hit percentage is the share of work area workload served fully in memory.

• maximum PGA allocated is the high water mark. Keep it under pga_aggregate_limit with headroom so the limit never fires in normal running.

One caveat the aggregate number hides. A single serial work area is capped at a fraction of pga_aggregate_target, governed internally by _smm_max_size. A very large sort can still spill to temp even when the overall target looks generous, so for heavy batch I sometimes raise the target specifically to lift that per-process ceiling. v$sql_workarea_histogram shows the optimal, one-pass, and multi-pass split if you want evidence before touching anything.

Applying the numbers

Moving off AMM to a 24 GB SGA and an 8 GB PGA target looks like this.

-- Switch from AMM to ASMM with explicit SGA and PGA
ALTER SYSTEM SET memory_target        = 0    SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size         = 24G  SCOPE=SPFILE;  -- static, needs restart
ALTER SYSTEM SET sga_target           = 24G  SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 8G   SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_limit  = 16G  SCOPE=SPFILE;
-- SHUTDOWN IMMEDIATE;  STARTUP;

sga_max_size is static, so this set needs an instance bounce. sga_target, pga_aggregate_target, and pga_aggregate_limit are all dynamic and accept SCOPE=BOTH once the instance is running the new ceiling. On Linux, pair the SGA figure with HugePages sized to match and set use_large_pages=ONLY so the instance refuses to start rather than quietly falling back to 4 KB pages.

Pull v$sga_target_advice and v$pga_target_advice during a representative workload, size each pool to the knee of its own curve, and set pga_aggregate_limit with real headroom so a runaway query gets capped instead of paging the whole box.