SGA/PGA Sizing, Buffer Cache, Shared Pool, Large Pool, Result Cache, and Memory Advisors

Learning outcomes

ServiceHub starts paging after a deployment. One engineer proposes “give 80% of RAM to the SGA,” another says “hit ratio must exceed 99%,” and a third doubles PGA_AGGREGATE_TARGET. None has first proved which memory pool is constrained or whether the host is swapping. Oracle memory tuning begins with a model: the System Global Area (SGA) is shared instance memory; the Program Global Area (PGA) is process/session-private memory; SQL workareas for sorts/hashes use PGA and can spill to TEMP when they cannot operate optimally in memory.

1. SGA and PGA solve different problems

The first sizing rule is not a percentage. It is that the whole database plus OS/agents/container overhead must fit physical memory without damaging paging/swapping. On Free, the database itself is capped at 2 GB RAM, so oversizing one pool simply starves another within a small envelope.

2. Identify the active memory management mode

SELECT name,value,isdefault,issys_modifiable,ispdb_modifiableFROM v$parameterWHERE name IN (  'memory_target',  'memory_max_target',  'sga_target',  'sga_max_size',  'pga_aggregate_target',  'pga_aggregate_limit',  'db_cache_size',  'shared_pool_size',  'large_pool_size',  'result_cache_max_size',  'result_cache_mode',  'workarea_size_policy')ORDER BY name;

If MEMORY_TARGET is nonzero, Automatic Memory Management (AMM) can redistribute memory between SGA and PGA. With SGA_TARGET plus PGA_AGGREGATE_TARGET, Automatic Shared Memory Management (ASMM) manages SGA components while PGA has its own target. Nonzero component sizes under SGA_TARGET can act as minimums, reducing Oracle's freedom to rebalance.

3. Measure current SGA/PGA state

SELECT name,bytes,resizeableFROM v$sgainfoORDER BY bytes DESC NULLS LAST;

SELECT name,value,unitFROM v$pgastatWHERE name IN (  'aggregate PGA target parameter',  'aggregate PGA auto target',  'total PGA allocated',  'total PGA inuse',  'maximum PGA allocated',  'over allocation count',  'cache hit percentage',  'extra bytes read/written')ORDER BY name;

over allocation count increasing during the incident is stronger evidence of PGA target pressure than one cache-hit percentage. PGA_AGGREGATE_TARGET is a target, not a hard cap; PGA_AGGREGATE_LIMIT is the protective ceiling that can terminate calls/sessions when total PGA runs dangerously high.

4. Advisors estimate tradeoffs; they are not commands

SELECT  memory_size,  memory_size_factor,  estd_db_time,  estd_db_time_factor,  versionFROM v$memory_target_adviceORDER BY memory_size;

SELECT  size_for_estimate,  size_factor,  estd_physical_read_factor,  estd_physical_readsFROM v$db_cache_adviceWHERE name='DEFAULT'  AND block_size=(SELECT value FROM v$parameter WHERE name='db_block_size')ORDER BY size_for_estimate;

SELECT  shared_pool_size_for_estimate,  shared_pool_size_factor,  estd_lc_time_saved,  estd_lc_load_timeFROM v$shared_pool_adviceORDER BY shared_pool_size_for_estimate;

SELECT  pga_target_for_estimate,  pga_target_factor,  estd_pga_cache_hit_percentage,  estd_extra_bytes_rw,  estd_overalloc_countFROM v$pga_target_adviceORDER BY pga_target_for_estimate;

Advisor rows model “what if” behavior from observed workload. They do not include every OS effect, future workload shift or application SLO. At STATISTICS_LEVEL=BASIC some advisor data is disabled/not maintained, another reason not to use BASIC as a casual “performance optimization.”

5. Result cache is a reuse mechanism, not generic more-cache-is-better memory

SELECT DBMS_RESULT_CACHE.STATUS() AS result_cache_statusFROM dual;SELECT name,valueFROM v$result_cache_statisticsORDER BY name;

The server result cache resides in shared-pool memory. Default RESULT_CACHE_MODE on ordinary non-Autonomous databases is normally MANUAL, so SQL must opt in (for example with the result-cache hint) unless policy is changed. Cache only deterministic, reuse-heavy results whose invalidation/correctness behavior is understood.

6. Host/container memory is part of the evidence

Oracle cannot tell whether Windows/Linux/container host memory is swapping heavily by looking only at SGA/PGA views. Capture host evidence at the same timestamps. A database whose SGA “hit ratio” looks excellent can still be slow because its memory pages are being reclaimed/swapped by the host.

free -hvmstat 1 10# Container limit/usage examples depend on cgroup version:cat /sys/fs/cgroup/memory.max 2>/dev/nullcat /sys/fs/cgroup/memory.current 2>/dev/null

Get-Counter '\Memory\Available MBytes',            '\Memory\Pages/sec' -SampleInterval 1 -MaxSamples 10

Do not compare Windows Pages/sec directly with Linux swap counters as if they were identical metrics; use platform-specific interpretation and correlate with Oracle response time.

7. Deliberately wrong: force an oversized SGA because the cache hit ratio is below 99%

A hit ratio ignores whether the physical reads are useful, sequential, prefetched or caused by an inefficient SQL plan. Enlarging SGA inside a memory-constrained host can trigger paging and make response time worse. On Free, an attempted memory configuration beyond the product's 2 GB database-memory limit can also fail or be constrained by the Free resource ceiling.

The repair is to capture workload + DB time/CPU/waits + physical reads + advisors + host paging. Tune SQL/access path first if a statement touches too many blocks. Change memory only when the evidence predicts a benefit and the host has headroom.

8. Free evidence worksheet

SELECT SYSTIMESTAMP AS captured_at FROM dual;SELECT name,valueFROM v$parameterWHERE name IN (  'memory_target','sga_target',  'pga_aggregate_target','pga_aggregate_limit',  'workarea_size_policy')ORDER BY name;SELECT name,bytesFROM v$sgainfoORDER BY bytes DESC NULLS LAST;SELECT name,value,unitFROM v$pgastatWHERE name IN (  'total PGA allocated',  'maximum PGA allocated',  'over allocation count',  'extra bytes read/written')ORDER BY name;

Save this output with host/container memory counters and representative load. A proposed change should state the observed symptom, expected mechanism, exact parameter/scope, before/after window and rollback value.

9. Production judgment

Use automatic memory management where appropriate, but do not treat it as proof the total memory budget is correct. Measure paging, PGA spill/over-allocation, parse/shared-pool evidence and physical I/O before resizing. Keep PGA_AGGREGATE_LIMIT as a safety boundary and test large-sort/hash/batch behavior before tightening it.

Current baseline is 26ai RU 23.26.3. Free's 2 GB database-memory ceiling materially limits tuning experiments. Most target parameters are dynamic, but maximum/startup allocations can require restart depending on the parameter/mode; always read ISSYS_MODIFIABLE and SPFILE state. No COMPATIBLE change or management pack is required for the mandatory lab. Lesson 2 asks the next question: if memory is not the bottleneck, what I/O is Oracle actually doing?

Authoritative references

• Managing Memory — AMM/ASMM/PGA and paging guidance
• V$MEMORY_TARGET_ADVICE — AMM advisor
• V$PGA_TARGET_ADVICE — PGA target estimates
• V$DB_CACHE_ADVICE — buffer-cache estimates
• Using the Server Result Cache — result-cache behavior