Oracle Architecture Overview

3.1 Big Picture

┌─────────────────────────── INSTANCE ────────────────────────────┐
 │  SGA (System Global Area - shared)                               │
 │  ┌────────────┐ ┌─────────────┐ ┌──────────┐ ┌──────────────┐    │
 │  │Buffer Cache│ │ Shared Pool │ │Redo Log  │ │ Large / Java │    │
 │  │(data blocks│ │ Library $,  │ │ Buffer   │ │ / Streams    │    │
 │  │ )          │ │ Dictionary $│ │          │ │ Pools        │    │
 │  └────────────┘ │ Result $    │ └──────────┘ └──────────────┘    │
 │                 └─────────────┘   In-Memory Area (optional)      │
 │  Background processes: PMON SMON DBWn LGWR CKPT ARCn MMON MMAN   │
 │                        RECO LREG CJQ0 ...                        │
 └──────────────────────────────────────────────────────────────────┘
   Server processes (dedicated / shared) each with a PGA (private)
                              │
 ┌─────────────────────────── DATABASE (files) ────────────────────┐
 │  Datafiles   Control files   Online redo logs   Temp files       │
 │  (+ Archived redo logs, SPFILE, password file, alert log, ...)   │
 └──────────────────────────────────────────────────────────────────┘

3.2 Memory Structures

Database buffer cache Caches data blocks (DEFAULT, KEEP, RECYCLE, nK pools) Shared pool

Library cache (parsed SQL and PL/SQL), data dictionary cache, result cache, server result cache Redo log buffer

Circular buffer of change vectors before LGWR writes them Large pool

RMAN, parallel execution, shared server (UGA) Java in the database Streams pool

GoldenGate integrated capture, Advanced Queuing In-Memory area

Columnar store (Database In-Memory option) Private memory per server process: sort area, hash area, session memory, cursor state

3.3 Background Processes

Process monitor: cleans up failed processes (with CLMN/CLnn helpers)

System monitor: instance recovery, temp segment cleanup Database writer: writes dirty buffers to datafiles

Log writer: writes the redo buffer to online redo logs (on commit, every 3 s, 1/3 full)

Checkpoint: updates the control file and datafile headers Archiver: copies filled redo logs to the archive destination MMON / MMNL

Manageability monitor: AWR snapshots, alerts Memory manager (automatic memory resizing)

Resolves in-doubt distributed transactions Listener registration (formerly done by PMON) CJQ0 / Jnnn Job queue coordinator and slaves DIAG, DIA0, VKTM, GEN0

Diagnostics, hang detection, timekeeping

SELECT pname, description FROM v$process WHERE pname IS NOT NULL ORDER BY pname;
SELECT name, description FROM v$bgprocess WHERE paddr <> '00';

3.4 How a SQL Statement Is Processed

Parse: syntax check → semantic check (dictionary) → shared pool check (soft parse if found, hard parse if not) → optimization → row source generation.

Bind: bind variables are substituted.

Execute: blocks are read from the buffer cache (logical I/O) or disk (physical I/O). Changes generate undo and redo.

Fetch: rows are returned to the client (for SELECT).

3.5 How a COMMIT Works

The server process generates redo (and undo) → LGWR flushes redo to the online redo log → the commit is acknowledged.

DBWn writes the data blocks later (lazily). This is write-ahead logging and the fast commit mechanism.

The SCN (System Change Number) is Oracle's logical clock.

3.6 Physical vs Logical Storage

Logical:   Database → Tablespace → Segment → Extent → Oracle Block
Physical:  Datafile ────────────────────────────────→ OS Block

3.7 Data Dictionary and Dynamic Performance Views

Objects you own Objects you can access All objects in the database (needs privileges) All containers (multitenant)

Dynamic performance views (memory/control file) Global V$ across RAC instances

SELECT table_name FROM user_tables;
SELECT * FROM dictionary WHERE table_name LIKE '%CONSTRAINT%';
SELECT * FROM v$session WHERE username = 'HR';
SELECT * FROM v$parameter WHERE name LIKE 'sga%';

3.8 Connection Architectures

Dedicated server (one server process per session) vs shared server (dispatchers plus a shared pool of server processes).

Database Resident Connection Pooling (DRCP) and Proxy Resident Connection Pooling (PRCP) in 23ai.