Oracle Database Architecture

Every time you run a query in Oracle, a precise sequence of events happens behind the scenes — memory is allocated, processes are invoked, files are read, and results are returned. Understanding Oracle's architecture is not just academic knowledge for DBAs. It helps developers write better SQL, understand performance behaviour, make sense of error messages, and have intelligent conversations with the people who manage the systems they build on. This lesson walks through Oracle's three-layer architecture — memory, processes, and storage — and explains how they work together every time a query runs.

The Three Layers of Oracle Architecture

Oracle Database architecture is organised into three distinct layers. Each layer has a clear responsibility, and together they form everything Oracle does.

1. Memory structures — the SGA and PGA hold data, SQL plans, and session information in RAM so that the database can serve requests quickly without constantly reading from disk
2. Background processes — a set of operating system processes that run continuously, managing writing, recovery, monitoring, and cleanup without any user involvement
3. Storage structures — the physical files on disk where all data, metadata, and change history are permanently stored

When you submit a SQL statement, Oracle coordinates all three layers simultaneously. The statement is parsed and planned in memory, executed using background processes where needed, and reads and writes flow between RAM and the storage files on disk.

Memory Structures — SGA and PGA

Oracle uses two memory areas, each serving a different purpose.

The System Global Area (SGA) is shared memory — every connected session uses it simultaneously. It has three main components:

• Buffer Cache — stores copies of data blocks recently read from disk. When a query needs a row, Oracle checks the buffer cache first. If the block is already there (a cache hit), no disk read is needed. If it is not (a cache miss), Oracle reads it from disk and places it in the cache for future use.
• Shared Pool — stores recently parsed and compiled SQL statements and their execution plans. When the same SQL is run again, Oracle reuses the cached plan instead of parsing it from scratch — this is called a soft parse and is significantly faster than a hard parse.
• Redo Log Buffer — a circular buffer that temporarily holds redo entries (records of every change) before the LGWR background process writes them to the redo log files on disk.

The Program Global Area (PGA) is private memory — each session gets its own PGA that no other session can access. It holds that session's sort space, query execution state, bind variable values, and local variables. When a session ends, its PGA is released.

• A larger SGA means more data cached in memory and fewer disk reads — one of the most effective ways to improve Oracle query performance in production
• The PGA size affects how much sorting and hashing a session can do in memory before spilling to disk — complex analytical queries benefit from a larger PGA allocation
• Oracle 12c introduced Automatic Memory Management (AMM), which allows Oracle to manage SGA and PGA sizes dynamically within a total memory limit you set — removing the need to manually tune each component

Background Processes

Oracle runs a set of background processes automatically when the database instance starts. They handle the work that keeps the database consistent, recoverable, and performant — without requiring any action from users or applications.

The five most important background processes are:

• DBWn — Database Writer — writes modified (dirty) data blocks from the SGA buffer cache to the data files on disk. It does this in batches rather than immediately after every change, which improves write performance significantly.
• LGWR — Log Writer — writes redo entries from the redo log buffer in the SGA to the redo log files on disk. LGWR writes on every COMMIT, ensuring that committed changes are durable even before DBWn writes the data files.
• CKPT — Checkpoint — periodically signals DBWn to write all dirty blocks to disk and updates the control file and data file headers with the current checkpoint position. Checkpoints reduce the amount of redo that must be applied during crash recovery.
• SMON — System Monitor — performs crash recovery when the database instance is restarted after a failure. It reads the redo log and applies any committed changes that had not yet been written to the data files, then rolls back any uncommitted transactions.
• PMON — Process Monitor — monitors user sessions and cleans up after any session that terminates abnormally. It rolls back the failed session's uncommitted transactions, releases its locks, and frees its resources.

• The sequence LGWR writes before DBWn is deliberate and critical — it is called write-ahead logging and it is what allows Oracle to guarantee durability. If the server crashes, the redo log always contains a complete record of committed changes even if the data files were not yet updated.
• PMON is why Oracle cleans up automatically when a client application crashes mid-transaction — locks are released and uncommitted changes are rolled back without any manual intervention from a DBA.
• Additional background processes exist for specific features — ARCn (archiving redo logs), RECO (distributed transaction recovery), and others — but DBWn, LGWR, CKPT, SMON, and PMON are the five that run in every Oracle instance.

Storage Structures

Oracle organises its physical storage into three types of files. Each has a distinct role and all three must be present and intact for the database to open.

Data files (.dbf) store all actual data — every table row, index entry, and schema object. Data files are grouped into logical units called tablespaces. The SYSTEM tablespace stores Oracle's internal data dictionary. The SYSAUX tablespace stores auxiliary components. The USERS tablespace is the default location for user-created tables and indexes. Each tablespace can span one or more physical data files.

Control files are small but critical files that record the database name, the names and locations of all data files and redo log files, and the current state of the database including the last checkpoint. Oracle reads the control file every time it starts. If the control file is lost and no backup exists, the database cannot open. Oracle recommends multiplexing the control file — keeping identical copies in multiple locations — for this reason.

Redo log files record every change made to the database in sequence. They are written to by LGWR continuously. Oracle operates with a minimum of two redo log groups, writing to one while the other is available. When a log group fills, Oracle switches to the next — this is called a log switch. In archivelog mode, Oracle archives filled redo log groups before reusing them, enabling point-in-time recovery. In noarchivelog mode (the default in XE), filled logs are overwritten.

• Tablespaces are the logical layer that sits between the SQL schema objects you create (tables, indexes) and the physical data files on disk — when you create a table Oracle assigns it to a tablespace, and the tablespace writes to one or more data files
• The distinction between archivelog and noarchivelog mode matters for backup strategy — production databases almost always run in archivelog mode so that they can be recovered to any point in time; this is covered in depth in Lesson 35 (Backup and Recovery)
• Oracle 12c introduced the multitenant architecture — a Container Database (CDB) that can host multiple Pluggable Databases (PDBs). Oracle XE uses this architecture: XE is the CDB and XEPDB1 is the PDB where you do your work. Each PDB has its own schema objects and data but shares the same SGA, background processes, and physical files as the CDB.

How a Query Flows Through the Architecture

Seeing the three layers in action makes them concrete. Here is what happens from the moment you press Ctrl+Enter in SQL Developer to the moment results appear.

1. Parse — Oracle checks the Shared Pool to see if an identical SQL statement has already been parsed and compiled. If yes, it reuses the cached execution plan (soft parse). If no, it parses the SQL for syntax, checks object permissions, generates candidate execution plans, picks the lowest-cost plan, and stores it in the Shared Pool (hard parse).
2. Execute — Oracle follows the execution plan. For each data block the plan requires, Oracle checks the Buffer Cache. Blocks already in cache are read from memory. Blocks not in cache are read from the data files on disk and loaded into the Buffer Cache.
3. Fetch — Oracle returns the result rows to the client (SQL Developer) in batches. The client fetches rows until all results have been returned.
4. Commit (for DML) — if the statement is an INSERT, UPDATE, or DELETE, Oracle writes the change record to the Redo Log Buffer, then LGWR flushes it to the redo log files. The change is now durable. DBWn will write the modified data blocks to the data files in the next batch write.

• The parse-execute-fetch cycle applies to every SQL statement — understanding it helps you interpret execution plan output and performance metrics covered in Lesson 37 (Performance Tuning)
• Soft parses are far cheaper than hard parses — using bind variables in application code instead of literal values in SQL strings promotes plan reuse and significantly reduces parse overhead in high-frequency OLTP systems
• The fact that DBWn writes to data files asynchronously after COMMIT is why Oracle can sustain very high transaction throughput — commits are fast because only LGWR must write synchronously; the heavier data file writes happen in background batches

Oracle vs SQL Server — Architecture Comparison

Practice Questions

Quiz

Next up — Tablespaces & Data Files — Learn how Oracle organises physical storage into tablespaces and data files, and understand how to create and manage them as a developer and administrator.