System Global Area (SGA): Purpose and Core Components

The System Global Area (SGA) is the shared memory for an Oracle instance. Every server and background process reads from and writes to the SGA to coordinate work, cache hot data, and reduce I/O. Getting SGA design right yields fewer physical reads, faster commits, and predictable latency under load.

What lives in the SGA

• Database Buffer Cache - Holds data blocks read from datafiles. Write activity is deferred and batched by DBWn.
• Redo Log Buffer - Temporary home for redo entries describing all changes; LGWR flushes to redo logs to make commits durable.
• Shared Pool - Library Cache (parsed/optimized SQL & PL/SQL) and Data Dictionary Cache. Reduces hard parses and repetitive metadata I/O.
• Also commonly present - Large Pool (RMAN/parallel exec), Java Pool, Streams Pool, and (when enabled) the In-Memory Area for Database In-Memory columnar copies. A small Fixed SGA holds instance metadata.

Why the SGA matters

• Latency: serving from memory avoids disk seeks. Correct cache sizing and hot-object pinning reduce wait events.
• Throughput: redo is flushed sequentially by LGWR; DBWn writes dirty buffers asynchronously, keeping user sessions non-blocking.
• Plan stability: a healthy shared pool prevents hard-parse storms and latch contention.

Sizing strategies (modern parameters)

• ASMM (Automatic Shared Memory Management): set SGA_TARGET and SGA_MAX_SIZE. Oracle distributes among buffer cache, shared pool, etc.
• AMM (Automatic Memory Management): MEMORY_TARGET/MEMORY_MAX_TARGET governs SGA+PGA together (platform/policy dependent; many shops prefer ASMM + explicit PGA).
• Complement with PGA_AGGREGATE_TARGET (and an appropriate PGA_AGGREGATE_LIMIT), and consider Keep/Recycle buffer pools for predictable caching of critical or one-time-scan objects.

Result caching: when it helps

SQL Query Result Cache can store results of deterministic, frequently repeated queries in the SGA so subsequent executions avoid both logical and physical I/O. Oracle invalidates cached results on relevant DML, so use it for relatively stable reference data, lookups, and dashboard aggregates.

-- Session-level preference (optional)
ALTER SESSION SET result_cache_mode = MANUAL;  -- or FORCE

-- Example: cache a stable aggregate
SELECT /*+ RESULT_CACHE */ deptno, COUNT(*) AS cnt
FROM   emp
GROUP  BY deptno;

-- Quick visibility into memory usage (views may vary by version)
SELECT name, bytes FROM v$result_cache_memory_info;

• Good fits: small/medium result sets, reference lists, star-schema lookups, dashboards with controlled refresh.
• Avoid: highly volatile tables or queries sensitive to session state (NLS settings, temp tables, etc.).
• Guardrails: if RESULT_CACHE_MAX_SIZE=0 the feature is effectively off; monitor hit ratios and invalidations.

Practical checklist

1. Size SGA_TARGET with headroom; watch buffer cache hit ratio and parse stats rather than chasing a single “magic” number.
2. Pin truly critical packages and consider a Keep pool for small, hot tables and their indexes.
3. Use Result Cache selectively on predictable, read-mostly queries; verify correctness under DML and session settings.
4. Re-baseline after major workload changes; memory that’s perfect today can be wrong tomorrow.