In the early days of database administration, setting memory was a guessing game. If you gave too much to the SGA, your PGA starved, and your users' sorts crashed. If you gave too much to the PGA, your data cache shrank, and the whole system slowed to a crawl.

Modern Oracle Databases have solved this with Dynamic Memory Management. Instead of static buckets, Oracle uses a "fluid" approach where memory flows to wherever the demand is highest.

1. The Three Levels of Automation

Oracle offers three ways to handle memory, ranging from "Micro-managed" to "Hands-off."

A. Automatic Memory Management (AMM)

This is the "Full Auto" mode. You set one single parameter: MEMORY_TARGET.

• How it works: Oracle takes a giant pool of RAM and decides how much to give to the SGA (shared) and how much to the PGA (private).

• The Benefit: If you have a sudden burst of complex queries, Oracle shrinks the data cache and expands the PGA. When the queries finish, it moves the memory back.

B. Automatic Shared Memory Management (ASMM)

This focuses only on the SGA. You set the SGA_TARGET parameter.

• How it works: Oracle automatically sizes the sub-components of the SGA (Buffer Cache, Shared Pool, etc.).

• The Benefit: You don't have to worry if your SQL cache is too small or your data cache is too big; Oracle balances them internally.

C. Automatic PGA Memory Management

You set the PGA_AGGREGATE_TARGET.

• How it works: Oracle doesn't give a fixed amount to each user. Instead, it tries to ensure the sum of all user PGAs doesn't exceed your target. It gives more memory to "heavy" queries and less to "light" ones.

2. The "Resize" Mechanism: How it moves

How does memory actually move without crashing the database? Oracle uses Granules.

A Granule is a contiguous unit of memory (typically 4MB, 16MB, or 64MB depending on your system size).

1. The Memory Advisor (a background process) constantly monitors "miss rates."

2. If it sees the Shared Pool is struggling while the Buffer Cache has idle memory, it "steals" a granule.

3. It de-allocates the granule from the Buffer Cache and re-assigns it to the Shared Pool instantly.

3. Comparing the Management Styles

4. The Memory Advisors

Oracle doesn't just guess; it uses Advisors. You can query these views to see "What If" scenarios:

• V$SGA_TARGET_ADVICE: Tells you: "If you gave me 2GB more RAM, physical reads would drop by 20%."

• V$PGA_TARGET_ADVICE: Tells you: "If you cut my memory in half, your disk-sorting will increase by 50%."

Peer Tip: While AMM (MEMORY_TARGET) is great, many high-performance Enterprise environments prefer ASMM (SGA_TARGET). This is because AMM uses /dev/shm on Linux, which can sometimes conflict with "HugePages"—a Linux feature used to boost performance for massive databases.