Oracle DBA Lesson 6A — The SGA Memory Map

Oracle DBA · Lesson 6A

The SGA Memory Map

The SGA is shared instance memory — not one blob. Map the neighborhoods: buffer cache (blocks), shared pool (SQL + dictionary), redo log buffer (change descriptions waiting for disk).

Watch on YouTube

Oracle DBA Lesson 6A — The SGA Memory Map

AI Ops Pro

Shared memory for the whole instance

The System Global Area (SGA) is memory every server and background process can see. It is allocated when the instance starts (and can resize later). When someone says the instance is “using 32 GB,” that figure is a city of neighborhoods — not one interchangeable pile.

Three neighborhoods that matter first

Start with these three. Later lessons zoom into each:

• Database buffer cache — copies of data blocks.
• Shared pool — parsed SQL and dictionary metadata.
• Redo log buffer — redo (change descriptions) waiting for disk.

Other pools exist (large pool, Java pool, Streams / AQ pool, In-Memory area when enabled). Lesson 6A is the map of the main streets.

One SGA for the CDB

PDBs share the instance SGA. You do not get a separate buffer cache per PDB by default. You can reserve or control some memory usage, but it is still one instance map.

Look with parameters and views

Confirm sizes and component names before you argue about which neighborhood is hungry:

SHOW PARAMETER sga

Lists SGA-related parameters for this instance.

SELECT name, value FROM v$sga;

High-level SGA component sizes.

SELECT pool, name, bytes
FROM   v$sgastat
WHERE  name IN ('buffer_cache', 'log_buffer', 'free memory')
   OR  pool IS NOT NULL
FETCH FIRST 20 ROWS ONLY;

Breaks the map into named pools and free memory. Adjust the filter as you explore.

Sizing intuition

Too small → extra I/O and extra parse work. Too large → OS paging, which is usually worse. Automatic Memory Management, Automatic Shared Memory Management, and manual sizing are three ways to size the city — know the knobs exist; do not start on hidden parameters. Lesson 6B goes deeper on sizing and inspection.

Gotchas

• Do not treat “SGA size” as one tunable that fixes every wait.
• Redo for an update lands in the redo log buffer, not first in the buffer cache as the redo record.
• Confirm with v$sga / v$sgastat before blaming the wrong neighborhood.

Quick quiz

Choose one answer per question, then submit. You’ll see the correct answer and a short why.

1. Which SGA neighborhood holds copies of data blocks?

Shared pool Database buffer cache Redo log buffer PGA only

2. Where do parsed SQL and dictionary metadata live?

Shared pool Database buffer cache Redo log buffer Only in PDB$SEED

3. A session updates a row. Where does the redo for that update land first?

Database buffer cache, as the redo record PGA workarea only Redo log buffer Control file

4. In a CDB, how many SGAs do the PDBs use?

One private SGA per PDB always One SGA per tablespace No SGA — only PGA They share the instance SGA

Website: https://www.apps-dba.com/

X: https://x.com/AIOpsPro