Oracle DBA Lesson 6B — Sizing and Inspecting the SGA

Oracle DBA · Lesson 6B

Sizing and Inspecting the SGA

After the SGA map (Lesson 6A), size the city and inspect it with parameters and views — ASMM / SGA_TARGET, and how to read V$SGA / V$SGASTAT.

Watch on YouTube

Oracle DBA Lesson 6B — Sizing and Inspecting the SGA

AI Ops Pro

From map to sizing

Lesson 6A named the neighborhoods. This lesson is how you size the shared area and confirm what Oracle allocated — without inventing live numbers or chasing one wait with a random memory bump.

Three ways to size the city

• AMM (Automatic Memory Management) — one target for SGA + PGA together (MEMORY_TARGET). Oracle balances shared and private memory under that ceiling.
• ASMM (Automatic Shared Memory Management) — a target for the SGA (SGA_TARGET). Oracle distributes memory among SGA components. PGA is sized separately.
• Manual — you set individual SGA component sizes yourself. No automatic redistribute inside the SGA.

Setting MEMORY_TARGET (greater than zero) enables AMM. When MEMORY_TARGET is unused / zero, setting SGA_TARGET enables ASMM (19c-style teaching). Know what each mode does; this is not a production recipe.

Parameters to show first

SHOW PARAMETER sga

Lists SGA-related parameters for this instance — look for sga_target and sga_max_size.

SHOW PARAMETER memory

Lists memory-related parameters — look for memory_target and memory_max_target.

The *_MAX_* values are ceilings; the *_TARGET values are what automatic management aims for within those limits.

Inspect with views

SELECT name, value FROM v$sga;

High-level SGA component sizes for this instance.

SELECT * FROM v$sgainfo;

Named SGA facts in one place (sizes, resizeable flags, and related info — filter columns as you explore).

SELECT pool, name, bytes
FROM   v$sgastat
WHERE  name = 'free memory'
   OR  pool IS NOT NULL
ORDER  BY bytes DESC
FETCH FIRST 25 ROWS ONLY;

Breaks the map into pools and named consumers (plus free memory). Sort and filter to see top consumers — do not invent sample byte values from a blog.

Sizing intuition

Too small → more physical I/O and more parse / shared-pool pressure. Too large → OS paging risk, which is usually worse than a slightly tight cache. Leave headroom for PGA and the OS. Confirm with views before you change knobs.

Gotchas

• Do not chase one wait with random SGA growth.
• Under ASMM, Oracle can move memory between SGA components — watch advice and the views, not a single snapshot rumor.
• PDBs share one instance SGA.
• Never invent live numbers; read them from your instance.

Quick quiz

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

1. What is the main difference between AMM and ASMM?

AMM sizes only the redo log buffer; ASMM sizes only the buffer cache AMM balances SGA + PGA under MEMORY_TARGET; ASMM auto-distributes inside the SGA under SGA_TARGET They are the same parameter with two names ASMM always requires MEMORY_TARGET greater than zero

2. What does SGA_TARGET primarily control?

Automatic distribution of memory among SGA components (ASMM) Only the size of each PDB’s private SGA Only PGA workarea size The OS page file size

3. Which view breaks the SGA into pools and named consumers?

V$SESSION only V$SGA alone (high-level totals only) V$SGASTAT V$CONTROLFILE

4. Too-small SGA vs too-large SGA — which intuition is sound?

Too large only causes more parse calls; paging never matters Too small is always better than leaving headroom for PGA SGA size never affects I/O Too small → more I/O / parse pressure; too large → OS paging risk (usually worse)

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro