MEMORY ARCHITECTURE

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

A. SGA (System Global Area) – Shared Memory

Purpose: Shared by all users and processes. Allocated at instance startup.

Main Components:

1. Database Buffer Cache

• Stores copies of data blocks read from datafiles
• When you query data, Oracle first checks here (not disk)
• Size parameter: DB_CACHE_SIZE or auto-managed by SGA_TARGET
• Uses LRU algorithm (Least Recently Used for eviction)
• Types of buffers:
  • Dirty buffers: Modified but not yet written to disk
  • Clean buffers: Not modified or already written to disk
  • Pinned buffers: Currently being accessed
• DBA concern: Too small = physical reads increase = slow queries

2. Shared Pool

a) Library Cache: Stores parsed SQL, PL/SQL code (execution plans)

• Prevents re-parsing same queries (soft parse vs hard parse)
• Hard parse = expensive (CPU intensive, takes locks)
• Soft parse = reuses existing plan (fast)
• Components: SQL area, PL/SQL area, Java pool cache

b) Data Dictionary Cache (Row Cache):

• Metadata about tables, users, privileges, tablespaces
• Cached from SYS schema objects

c) Result Cache (19c):

• Stores query results for reuse
• Can dramatically improve performance for repetitive queries
• Size parameter: SHARED_POOL_SIZE
• DBA concern: Insufficient size = hard parses spike = CPU saturation

3. Redo Log Buffer

• Small circular buffer for redo entries (change vectors)
• LGWR writes it to redo log files
• Size parameter: LOG_BUFFER (typically 10-16 MB is enough)
• Rarely needs tuning in modern systems
• DBA concern: If too small, LGWR can’t keep up = “log file sync” waits

4. Large Pool (Optional but recommended)

• Used for RMAN backup/restore operations
• Parallel execution message buffers
• Shared server (MTS) session memory
• Size parameter: LARGE_POOL_SIZE
• Best practice: Set to at least 128MB if using RMAN

5. Java Pool (If using Java in DB)

• For Java stored procedures, SQLJ
• Size parameter: JAVA_POOL_SIZE
• Not needed if you don’t use Java in database

6. Streams Pool (For replication/streams)

• Used by Oracle Streams, GoldenGate
• Size parameter: STREAMS_POOL_SIZE

7. Fixed SGA

• Internal housekeeping structures
• Contains general information about instance state
• You cannot control this size

INTERVIEW QUESTIONS:

Q1: What is SGA and its main components?
A: SGA is System Global Area—shared memory allocated at instance startup. Main components: Database Buffer Cache (data blocks), Shared Pool (parsed SQL, data dictionary), Redo Log Buffer (change vectors), and optional pools like Large Pool (RMAN) and Java Pool.

Q2: What’s the difference between hard parse and soft parse?
A:

• Hard parse: SQL is new, Oracle parses it, creates execution plan, stores in library cache. CPU intensive.
• Soft parse: SQL already in library cache, Oracle reuses existing plan. Very fast.
• Use bind variables to avoid hard parses.

Q3: How to reduce hard parses?
A:

1. Use bind variables instead of literals
2. Increase SHARED_POOL_SIZE
3. Set CURSOR_SHARING=FORCE (last resort, has side effects)
4. Check V$SQL for similar queries with different literals

Q4: What is stored in Database Buffer Cache?
A: Copies of data blocks read from datafiles. Includes table data, index data. Uses LRU algorithm. When data is modified, blocks become “dirty” until DBWn writes them to disk.

Q5: What happens when buffer cache is full?
A: Oracle uses LRU algorithm to evict least recently used clean buffers. If no clean buffers available, DBWn is signaled to write dirty buffers to disk, causing “free buffer waits” (performance issue).

B. PGA (Program Global Area) – Private Memory

Purpose: Private memory for each server process. NOT shared.

What’s stored in PGA:

• Session memory: Session variables, logon information, session cursor state
• Private SQL area: Bind information, runtime memory structures
• Sort area: For ORDER BY, GROUP BY, DISTINCT operations
• Hash area: For hash joins
• Bitmap merge area: For bitmap index operations

Size parameters:

• PGA_AGGREGATE_TARGET = Total PGA for all sessions (Oracle auto-manages individual allocations)
• PGA_AGGREGATE_LIMIT = Hard limit (19c feature to prevent runaway processes from consuming all memory)
• WORKAREA_SIZE_POLICY=AUTO (default, recommended)

How Oracle allocates PGA:

• Oracle estimates memory needed for each operation
• Distributes available PGA among active operations
• If insufficient PGA, operations spill to TEMP tablespace (slower)

DBA concern:

• Too small = sorts/joins spill to TEMP tablespace (disk-based, very slow)
• Monitor: V$PGASTAT, V$PROCESS, V$SQL_WORKAREA

INTERVIEW QUESTIONS:

Q1: What’s the difference between SGA and PGA?
A:

• SGA: Shared memory, allocated at instance startup, used by all processes. Contains buffer cache, shared pool.
• PGA: Private memory, allocated per server process, not shared. Contains sort areas, session variables, private SQL areas.

Q2: What happens if PGA is insufficient?
A: Sort and hash operations spill to TEMP tablespace (disk). This causes performance degradation. You’ll see “direct path read temp” and “direct path write temp” wait events.

Q3: How to identify if PGA is undersized?
A: Check:

SELECT name, value FROM V$PGASTAT 
WHERE name IN ('over allocation count', 'global memory bound');

If “over allocation count” > 0, increase PGA. Also check V$SQL_WORKAREA_ACTIVE for operations using disk.

Q4: What is PGA_AGGREGATE_LIMIT (19c)?
A: Hard limit on total PGA usage. If exceeded, Oracle terminates sessions consuming most PGA to protect instance. Prevents runaway queries from crashing the database. Default is 2x PGA_AGGREGATE_TARGET or 3GB (whichever is higher).

C. Automatic Memory Management (AMM vs ASMM)

Oracle 19c offers three memory management modes:

1. Automatic Shared Memory Management (ASMM) – RECOMMENDED

SGA_TARGET = 10G          -- Oracle auto-tunes components within this
SGA_MAX_SIZE = 12G        -- Maximum SGA can grow to (requires restart to increase)
PGA_AGGREGATE_TARGET = 3G -- Oracle manages PGA automatically

How it works:

• You set total SGA size
• Oracle automatically resizes buffer cache, shared pool, large pool, java pool
• You can still set minimums: DB_CACHE_SIZE, SHARED_POOL_SIZE (Oracle won’t go below these)
• Downtime needed: Only to increase SGA_MAX_SIZE

Best for: Most production systems, especially large databases

2. Automatic Memory Management (AMM)

MEMORY_TARGET = 13G       -- Total memory (SGA+PGA combined)
MEMORY_MAX_TARGET = 16G   -- Maximum total memory

How it works:

• Oracle manages BOTH SGA and PGA automatically
• Can transfer memory between SGA and PGA as needed
• More flexible but has overhead

Limitations:

• Not supported on Linux with huge pages enabled
• Performance overhead on very large systems (>40GB SGA)
• Downtime needed: Only to increase MEMORY_MAX_TARGET

Best for: Smaller databases (<20GB SGA), test/dev environments

3. Manual Memory Management

SGA_TARGET = 0                    -- Disables ASMM
DB_CACHE_SIZE = 6G
SHARED_POOL_SIZE = 2G
LARGE_POOL_SIZE = 256M
LOG_BUFFER = 16M
PGA_AGGREGATE_TARGET = 3G

When to use:

• Very specific tuning requirements
• You know exact optimal sizes (rare)
• Legacy systems

Downtime needed: Changes to most SGA components require restart

CRITICAL RULES:

❌ NEVER SET BOTH:

-- WRONG - Don't mix AMM and ASMM
MEMORY_TARGET = 10G       -- AMM
SGA_TARGET = 8G           -- ASMM (will be ignored!)

✅ CORRECT COMBINATIONS:

Option A: Use ASMM (Recommended for 19c)

SGA_TARGET = 10G
SGA_MAX_SIZE = 12G
PGA_AGGREGATE_TARGET = 3G
PGA_AGGREGATE_LIMIT = 6G
MEMORY_TARGET = 0         -- Explicitly disable AMM

Option B: Use AMM

MEMORY_TARGET = 13G
MEMORY_MAX_TARGET = 16G
SGA_TARGET = 0            -- Must be 0 or not set
PGA_AGGREGATE_TARGET = 0  -- Must be 0 or not set

Option C: Manual

SGA_TARGET = 0
MEMORY_TARGET = 0
DB_CACHE_SIZE = 6G
SHARED_POOL_SIZE = 2G
PGA_AGGREGATE_TARGET = 3G

PARAMETER CHANGE – DOWNTIME REQUIREMENTS:

How to change dynamically:

-- Check if parameter is dynamic
SELECT name, value, isses_modifiable, issys_modifiable 
FROM v$parameter 
WHERE name = 'sga_target';

-- Change SGA_TARGET online (within SGA_MAX_SIZE limit)
ALTER SYSTEM SET sga_target=12G SCOPE=BOTH;

-- To exceed SGA_MAX_SIZE, must restart
ALTER SYSTEM SET sga_max_size=16G SCOPE=SPFILE;  -- Change in SPFILE
SHUTDOWN IMMEDIATE;
STARTUP;

INTERVIEW QUESTIONS:

Q1: Can I set both MEMORY_TARGET and SGA_TARGET?
A: No. If you set MEMORY_TARGET (AMM), you must leave SGA_TARGET=0. If you set SGA_TARGET (ASMM), you must leave MEMORY_TARGET=0. They are mutually exclusive. Setting both causes Oracle to ignore SGA_TARGET.

Q2: Which is better – AMM or ASMM?
A: For 19c production systems, ASMM is recommended. AMM has overhead and isn’t supported with huge pages on Linux. ASMM gives you better control and performance for large SGA sizes (>20GB).

Q3: Can I increase SGA_TARGET without downtime?
A: Yes, but only up to SGA_MAX_SIZE. If you need to go beyond SGA_MAX_SIZE, you must change SGA_MAX_SIZE in SPFILE and restart the instance.

Q4: How to check current memory usage?
A:

-- SGA components
SELECT * FROM v$sga;
SELECT * FROM v$sgastat ORDER BY bytes DESC;

-- PGA usage
SELECT * FROM v$pgastat;
SELECT name, value/1024/1024 MB FROM v$pgastat 
WHERE name IN ('total PGA allocated', 'total PGA inuse');

-- Memory parameters
SHOW PARAMETER sga
SHOW PARAMETER pga
SHOW PARAMETER memory

Q5: What if I set SGA_TARGET=10G but SGA_MAX_SIZE=8G?
A: Database won’t start. You’ll get error: “ORA-00844: Parameter not taking MEMORY_MAX_TARGET into account”. SGA_MAX_SIZE must be >= SGA_TARGET.

Q6: Does changing PGA_AGGREGATE_TARGET require restart?
A: No. It’s a dynamic parameter. Change takes effect immediately for new sessions. Existing sessions adjust gradually.

Q7: I increased SGA_TARGET but free memory didn’t decrease on OS level. Why?
A: Oracle allocates up to SGA_MAX_SIZE at startup (on Linux). Increasing SGA_TARGET doesn’t allocate more OS memory—it just allows Oracle to use more of the already-allocated space.

Q8: How to switch from AMM to ASMM?
A:

-- First, note current values
SHOW PARAMETER memory_target
SHOW PARAMETER sga_target

-- Set ASMM parameters
ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=10G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size=12G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=3G SCOPE=SPFILE;

-- Restart required
SHUTDOWN IMMEDIATE;
STARTUP;