Mastering Oracle Memory Management: From Manual Tuning to Automatic and Large Pages

Overview

Oracle has provided several memory‑management approaches over the years, moving from manual SGA/PGA sizing to increasingly automated mechanisms such as Automatic Shared Memory Management (ASMM), Automatic Memory Management (AMM) and PGA automatic management. The article addresses common DBA questions about which method to use, how to size SGA and PGA, and when to enable large pages.

Memory Composition

On a server dedicated to Oracle, the operating‑system memory is typically divided among:

Kernel Memory

OS Page Table

File‑system cache

SGA (System Global Area)

PGA (Program Global Area)

Oracle server processes (shadow processes)

Other processes (RMAN, OS utilities, etc.)

Both SGA and PGA are critical for database performance and must be planned carefully to avoid OS swapping and RAC node eviction.

Evolution of Automatic Memory Management

Oracle's memory management has progressed as follows:

Pre‑9i: manual SGA and PGA sizing.

9i: introduction of PGA automatic management via PGA_AGGREGATE_TARGET.

10g: Automatic Shared Memory Management (ASMM) using SGA_TARGET.

11g: full Automatic Memory Management (AMM) controlled by MEMORY_TARGET, which also disables large‑page usage.

PGA Automatic Management

Oracle uses a multi‑process architecture; each client connection runs in its own server process, which consumes private OS memory for sorting, hashing, and other operations. Before 9i, DBA had to tune the following parameters for each process:

SORT_AREA_SIZE

HASH_AREA_SIZE

BITMAP_MERGE_AREA_SIZE

CREATE_BITMAP_AREA_SIZE

In 9i the single parameter PGA_AGGREGATE_TARGET began to control the total PGA usage of all server processes. The mode is selected with WORKAREA_SIZE_POLICY, which can be AUTO (automatic) or MANUAL (manual).

Typical PGA‑intensive operations include:

Hash joins – memory is taken from the private PGA (controlled by HASH_AREA_SIZE when manual).

Sorts – memory is taken from the private PGA (controlled by SORT_AREA_SIZE when manual).

Parallel queries – each parallel slave can use up to 2 GB of PGA; the total parallel PGA is limited to half of PGA_AGGREGATE_TARGET.

In 12c the automatic PGA management is mature; most workloads no longer require manual tuning, although DBA can still adjust hidden parameters for special cases: _PGA_MAX_SIZE – maximum PGA per process (default 2 GB). _SMM_MAX_SIZE – maximum work area size (default half of _PGA_MAX_SIZE). _SMM_PX_MAX_SIZE – total PGA allowed for all parallel slaves (default half of PGA_AGGREGATE_TARGET).

These parameters can be changed at session or system level.

Automatic Shared Memory Management (ASMM)

ASMM, introduced in Oracle 10g, automates SGA component sizing by setting SGA_TARGET to a non‑zero value. When DB_CACHE_SIZE and other component parameters are also set, they act as minimum values. ASMM stores the derived optimal values in the spfile with a double‑underscore prefix (e.g., __DB_CACHE_SIZE).

In Oracle 11g, even if SGA_TARGET and MEMORY_TARGET are zero, the database may still move memory between pools using the _MEMORY_IMM_MODE_WITHOUT_AUTOSGA parameter.

Automatic Memory Management (AMM)

AMM, available from Oracle 11g, requires only the MEMORY_TARGET parameter to control the total memory allocated to the instance. AMM disables the use of large pages, and creates hidden parameters such as __PGA_AGGREGATE_TARGET and __SGA_TARGET. For systems with less than 4 GB RAM, DBCA defaults to AMM; otherwise it defaults to ASMM.

Large Pages

Large pages (huge pages) reduce the number of page‑table entries, pin the SGA in memory, and improve TLB hit rates. On Linux the default page size is 4 KB, which can cause massive page‑table memory consumption for large‑memory Oracle instances. Using large pages requires pre‑allocation via vm.nr_hugepages (2 MB units) in /etc/sysctl.conf or by writing to /proc/sys/vm/nr_hugepages. The recommended total large‑page allocation is greater than the sum of all SGA components plus 2 GB.

Benefits:

Fewer page‑table entries → lower kernel memory usage.

SGA is pinned, preventing swap‑outs.

Higher TLB hit rate → reduced CPU overhead.

Drawbacks:

Must be allocated in advance; may require a reboot.

Over‑allocation wastes memory that cannot be used by other processes.

Configuration example (Linux):

cat /proc/meminfo
HugePages_Total: 43000
HugePages_Free: 29493
HugePages_Rsvd: 23550
Hugepagesize: 2048 kB

The USE_LARGE_PAGES parameter controls large‑page usage. Values before 11.2.0.3 are TRUE, FALSE, ONLY; from 11.2.0.3 the AUTO option attempts to allocate as many large pages as possible.

Transparent Huge Pages (THP) are enabled by default on many modern kernels but are known to cause problems for Oracle; they should be disabled (e.g., transparent_hugepage=never in /etc/grub.conf or by echoing to /sys/kernel/mm/transparent_hugepage/enabled).

Best Practices

There is no one‑size‑fits‑all rule; DBA must consider workload type, number of active sessions, and SQL characteristics.

OLTP workloads: Reserve ~20 % of total RAM for the OS, allocate ~20 % of the remaining memory to PGA and ~80 % to SGA. If many concurrent sessions exist, increase PGA per session (≈12 MB).

OLAP/DSS workloads: After OS reservation, split remaining memory roughly 50/50 between PGA and SGA because buffer cache is less critical.

Prefer manual SGA tuning for complex environments, or ASMM with minimum values for DB_CACHE_SIZE and SHARED_POOL_SIZE if manual tuning is too burdensome.

Avoid AMM when large pages are required, because AMM disables large‑page usage.

Monitor buffer‑cache hit ratio and library‑cache hit ratio; low values may indicate insufficient memory or poorly written SQL (e.g., missing bind variables).

Use AWR or V$PGASTAT to track PGA usage; for historical trends, query V$SYSSTAT or V$SYS_TIME_MODEL (or the VSYSMETRIC_HISTORY view in 12c).

When configuring large pages, ensure the total large‑page allocation exceeds the sum of SGA components plus 2 GB, and verify the setting with cat /proc/meminfo. Adjust USE_LARGE_PAGES to TRUE or AUTO and disable THP to avoid unexpected behavior.

Conclusion

Oracle’s memory‑management features have evolved toward greater automation, but DBAs still need a solid understanding of SGA, PGA, and large‑page concepts to make informed sizing decisions, avoid OS swapping, and achieve optimal performance for both OLTP and OLAP environments.