🧠
SGA
System Global Area
The SGA is a shared memory region allocated when the Oracle instance starts. It is shared by all server and background processes connected to the instance.
Shared Pool — SQL cache, data dictionary, library cache
Buffer Cache — caches data blocks from datafiles
Redo Log Buffer — holds redo entries before LGWR writes
Large Pool — UGA for shared server, parallel query
Java Pool — JVM memory for Java stored procedures
Shared Memory
Instance-wide
DBA_SGASTAT
👤
PGA
Program Global Area
Non-shared memory allocated per server process. Contains session-specific data: bind variables, sort areas, SQL work areas, and cursor state.
SQL Work Area — sort, hash join, bitmap operations
Session Memory — logon info, session variables
Private SQL Area — bind variables, query execution state
Per-Session
Private Memory
V$PROCESS
⚡
Shared Pool
SGA Sub-component
The most performance-critical SGA component. Caches parsed SQL (Library Cache) and Oracle metadata (Data Dictionary Cache). A soft parse reuses the cached parse tree — crucial for OLTP performance.
Library Cache — parsed SQL, execution plans, PL/SQL
Data Dictionary Cache — table/column/user metadata
Result Cache — query result reuse (11g+)
Hard Parse
Soft Parse
V$LIBRARYCACHE
💾
Buffer Cache
SGA Sub-component
Caches copies of data blocks from datafiles. Oracle always modifies data in the buffer cache first — dirty buffers are written to disk by DBWR asynchronously.
Clean buffers — exact copy of disk block
Dirty buffers — modified, not yet written to disk
Pinned buffers — currently being accessed
LRU / LRUW lists manage replacement policy
LRU Algorithm
Dirty Buffers
V$BH
📝
DBWR
Database Writer Process
Writes dirty buffers from the Buffer Cache to datafiles on disk. DBWR is lazy — it writes in large batches when checkpoints occur, when the buffer cache is full, or after a set threshold of dirty buffers.
Triggered by: checkpoint, buffer cache threshold
Writes to: .dbf datafiles via OS I/O
Multiple DBWR processes possible (DB_WRITER_PROCESSES)
Async Write
Checkpoint
V$BGPROCESS
📡
LGWR
Log Writer Process
The most critical background process for durability. LGWR writes redo entries from the Redo Log Buffer to online redo log files. A COMMIT does not return to the user until LGWR confirms the write.
Writes on COMMIT (synchronous — user waits!)
Writes when buffer 1/3 full or every 3 seconds
Writes before DBWR (Write-Ahead Logging)
WAL Protocol
Sync Write
V$LOG
🔧
SMON
System Monitor Process
The database janitor. SMON performs instance recovery at startup (rolls forward committed changes, rolls back uncommitted ones). Also coalesces free space in tablespaces.
Instance recovery: roll forward → roll back
Temporary segment cleanup
Dictionary-managed tablespace coalescing
Recovery
Coalescing
Auto Start
🧹
PMON
Process Monitor
Cleans up after failed user processes. If a session dies abnormally, PMON rolls back the uncommitted transaction, releases locks, and frees SGA resources — like a session garbage collector.
Rolls back dead session's transactions
Releases all held locks and latches
Registers the instance with listeners
Cleanup
Rollback
Registration
📋
Redo Log Files
Online Redo Logs
The Oracle lifeline. Every change to the database is recorded as a redo entry, enabling recovery from any failure. Oracle writes to groups of redo logs in a circular fashion.
Circular writing: group 1 → 2 → 3 → 1…
Archived (if ARCHIVELOG mode) for full recovery
Minimum 2 groups; mirror for protection
ARCHIVELOG
Recovery
V$LOGFILE