How to configure an Oracle database for optimal memory management (AMM/ASMM)

Selecting AMM can realize fully automatic memory management of SGA and PGA, which is suitable for environments that support this function; selecting ASMM is more suitable when fine control of PGA or using HugePages is required, and only the internal components of SGA are automatically managed.

Configuring an Oracle database for optimal memory management involves choosing between Automatic Memory Management (AMM) and Automatic Shared Memory Management (ASMM), then setting the appropriate initialization parameters. The right choice depends on your system environment, performance goals, and whether you're using Oracle Managed Files, container databases, or running on platforms like Linux with large memory capacity.

Understand AMM vs ASMM

Automatic Memory Management (AMM) allows Oracle to automatically manage both the SGA (System Global Area) and PGA (Program Global Area) from a single memory pool defined by MEMORY_TARGET . This simplifies tuning because Oracle dynamically redistributes memory between SGA and PGA as needed.

Automatic Shared Memory Management (ASMM) , in contrast, only automates the distribution within the SGA (eg, shared pool, buffer cache). You must manually manage PGA usage via PGA_AGGREGATE_TARGET . ASMM uses SGA_TARGET to allow dynamic resizing of SGA components while keeping total SGA size under control.

Use AMM if you want full automation and are not on an environment that restricts its use (eg, some versions of Exadata or specific OS configurations). Use ASMM when you need more control over PGA or are on platforms where AMM is not supported (such as Linux with HugePages enabled, which typically requires fixed SGA sizing).

Enable Automatic Memory Management (AMM)

To configure AMM:

• Set MEMORY_TARGET to the total amount of memory Oracle can use for SGA and PGA combined. For example:
  ALTER SYSTEM SET MEMORY_TARGET = 4G SCOPE=SPFILE;
• Optionally set MEMORY_MAX_TARGET if you want to define the maximum possible value for MEMORY_TARGET without restarting the instance:
  ALTER SYSTEM SET MEMORY_MAX_TARGET = 4G SCOPE=SPFILE;
• Ensure SGA_TARGET and PGA_AGGREGATE_TARGET are either unset or automatically derived — Oracle will manage them internally.
• Restart the database for changes to take effect.

After enabling, monitor memory distribution using views like V$MEMORY_DYNAMIC_COMPONENTS and V$PGASTAT to confirm Oracle is balancing memory effectively.

Enable Automatic Shared Memory Management (ASMM)

If you prefer to keep manual control over PGA or cannot use AMM (eg, due to HugePages requirements):

• Set SGA_TARGET to enable dynamic sizing of SGA components:
  ALTER SYSTEM SET SGA_TARGET = 3G SCOPE=SPFILE;
• Manually set PGA_AGGREGATE_TARGET based on workload:
  ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1G SCOPE=BOTH;
• Leave MEMORY_TARGET and MEMORY_MAX_TARGET unset to disable AMM.
• Adjust individual SGA pools (like DB_CACHE_SIZE , SHARED_POOL_SIZE ) only if necessary — otherwise, let Oracle handle internal distribution.

Monitor SGA component sizes using V$SGA_DYNAMIC_FREE_MEMORY and V$SGA_RESIZE_OPS to ensure smooth resizing operations.

Best Practices and Monitoring

Regardless of the method chosen:

• Avoid mixing AMM and manual memory parameters. Setting MEMORY_TARGET disables manual SGA/PGA tuning.
• On systems using HugePages (common in Linux for performance), AMM is not recommended because it uses regular memory pages. In such cases, use ASMM with fixed SGA size.
• Size MEMORY_TARGET or SGA_TARGET PGA_AGGREGATE_TARGET to about 60–80% of total physical RAM, leaving room for OS and other processes.
• Use AWR reports and V$MEMORY_USAGE to analyze memory pressure and adjust settings accordingly.
• Test configuration changes in non-production environments first.

Proper memory configuration reduces parsing overhead, improves cache hit ratios, and minimizes disk I/O. Tune incrementally and validate performance impacts after each change.

Basically, choose AMM for simplicity and full automation when supported, or ASMM for finer control — especially in high-performance or specialized environments.

The above is the detailed content of How to configure an Oracle database for optimal memory management (AMM/ASMM). For more information, please follow other related articles on the PHP Chinese website!