After the initial overview of memory monitoring in Oracle, particularly around the SGA and PGA, I wanted to take a deeper dive into the most common Oracle memory configurations. These include how memory settings are defined, how they interact, and the combinations you’re likely to encounter across different workloads.  Oracle memory is a no-brainer for many of us who’ve been in the space for an extensive time, but I’ve come to realize, it can be a bit overwhelming and especially with recent changes from Oracle 12c on.

How Memory Settings Are Applied in Oracle

Oracle memory parameters can be configured in three main ways:

• Initialization Parameters via SPFILE or INIT.ORA files
• Dynamic Parameter Changes using ALTER SYSTEM
• Session-Level Changes using ALTER SESSION

These settings are applied through several mechanisms:

1. Database startup with SPFILE – the default approach on most modern systems.
2. Startup using a specified INIT.ORA file – usually for specific configurations or legacy systems.
3. ALTER SYSTEM SET – dynamically adjusts parameters when allowed.
4. ALTER SYSTEM SET … SCOPE=SPFILE – for static parameters requiring a database restart.
5. ALTER SESSION SET – for changing parameters just within a session, useful for unique processing demands.

Important: You can only set memory values that your system has available. If you specify more memory than is physically available, especially for parameters like SGA_*, PGA_*, or HUGE_PAGES, the database won’t start and will return an error.  This includes the overhead to support the operating system.   It’s common practice to not run other software on a host that is running Oracle.  I have only rarely experienced a host that has other applications installed.   Oracle SGA is most commonly much LARGER than the PGA.  SGA is handling a lot more of the memory demands on a system, including housing all the caches and pools managing different aspects of memory function in Oracle.  A small, initial Oracle database will have 4G of SGA, with 1G of PGA, so keep that in mind when you’re learning about SGA vs. PGA.  I’ve worked on database with terabytes of SGA and 100’s of GB of PGA, so depending on the workload, memory requirements for an Oracle database can be substantial.

To check current values, log into SQL*Plus or SQLcl and run the following command:

SHOW PARAMETER SGA

To make changes:

ALTER SYSTEM SET SGA_TARGET=8900M SCOPE=SPFILE;

(Note: This change takes effect after a database restart.)

Automatic Memory Management (AMM) vs. Automatic Shared Memory Management (ASMM)

Terms like Automatic Memory Management (AMM) and Automatic Shared Memory Management (ASMM) are commonly used in Oracle circles, but it’s important to understand these are not single parameters. They refer to sets of parameters working in conjunction.

Automatic Memory Management (AMM)

Introduced in Oracle 11g, AMM allows Oracle to dynamically manage both SGA and PGA with two parameters:

• MEMORY_TARGET
• MEMORY_MAX_TARGET

The idea was to simplify memory tuning by consolidating everything into a single memory pool. However, AMM often backfires in real-world scenarios. DBAs have frequently encountered memory thrashing, where memory is reallocated too aggressively between SGA components and/or PGA, leading to degraded performance. Oracle even discourages its use in large or high-performance systems.

Automatic Shared Memory Management (ASMM)

ASMM offers a more stable and controlled approach. It introduces:

• SGA_TARGET
• SGA_MAX_SIZE

PGA is managed independently via:

• PGA_AGGREGATE_TARGET
• PGA_AGGREGATE_LIMIT

This separation respects the distinct workloads of the SGA (shared memory) and PGA (process-local memory), giving DBAs more control while still allowing Oracle to fine-tune caches and pools internally. ASMM is now the recommended standard for most workloads.

AMM is not compatible with Huge Pages. For performance-focused environments, especially those using Huge Pages, AMM is not an option.

Manual Memory Management

Some workloads require tighter control. Whether due to legacy application demands, technical debt, or highly specialized workloads, manual tuning still has its place. This involves explicitly setting parameters like:

• DB_CACHE_SIZE
• SHARED_POOL_SIZE
• LARGE_POOL_SIZE
• JAVA_POOL_SIZE
• LOG_BUFFER

You’ll occasionally see deprecated parameters like:

• HASH_AREA_SIZE
• SORT_AREA_SIZE

These predate modern PGA management and are generally unnecessary in current releases. However, in niche scenarios often involving legacy code, they might still show up. Even then, Oracle’s PGA usually overrides these values.

HUGE_PAGES in Oracle

A crucial part of memory optimization in Oracle is understanding and implementing Huge Pages.

What Are Huge Pages?

By default, Linux memory pages are 4KB. Every page requires a separate entry in the page table, which the OS and CPU use to translate virtual to physical memory. In a large database environment, this leads to a massive number of entries and performance overhead.

Huge Pages, typically sized between 2MB and 1GB, drastically reduce this overhead by:

• Reducing the number of pages
• Minimizing TLB (Translation Lookaside Buffer) misses
• Locking memory into RAM (preventing swapping)

For Oracle’s SGA, this translates into significant performance and stability improvements.

How to Configure Huge Pages

Oracle includes a script to help you estimate Huge Page requirements:

vi $ORACLE_HOME/bin/hugepages_settings.sh

Once you’ve calculated the value:

vi /etc/sysctl.conf

vm.nr_hugepages = <calculated_value>

Add boot parameters (GRUB):

default_hugepagesz=2M hugepagesz=2M hugepages=<NUM_PAGES>

Important: Huge Pages are not supported with AMM. You must be using either ASMM or manual SGA settings.

Exadata and Huge Pages

With the release of Oracle 23.8.0, Huge Pages will be the default on Exadata and Small Pages support is being deprecated. This marks a strong push by Oracle toward maximizing memory efficiency and system performance.  For those of you using the MEMORY_TARGET init parameter, this means a change will be required on Exadata as they move forward.

Summary

Memory Management Style	PGA Managed	SGA Managed	Uses Huge Pages	Notes
AMM	✅	✅	❌	Simplifies tuning, but often unstable
ASMM	❌ (separate)	✅	✅	Recommended standard approach
Manual	❌ (separate)	✅ (granular)	✅	Required for legacy or specialized systems

 

Oracle’s memory configuration options are rich, flexible, and powerful, but that also means they require thoughtful planning and continued review of Oracle’s advisor features. Whether you’re configuring a new database or tuning a legacy one, understanding how memory parameters work together is essential to avoid pitfalls and ensure top performance.