ORACLE DATABASE ARCHITECTURE — BUFFER CACHE AND REDO LOG BUFFER
12. Database Buffer Cache
The Database Buffer Cache is one of the most important components of the Oracle SGA.
It is a memory area that stores copies of data blocks read from datafiles. Oracle uses the buffer cache to reduce physical I/O because accessing memory is much faster than repeatedly reading blocks from disk.
Oracle Session
|
v
SQL Statement
|
v
Database Buffer Cache
/ \
/ \
Block Found Block Not Found
(Hit) (Miss)
| |
v v
Read from Cache Read from Datafile
|
v
Buffer Cache
How Buffer Cache Works
Suppose we execute:
SELECT * FROM employees;
Oracle first needs the required data blocks.
The simplified process is:
SQL Query | v Check Buffer Cache | +---- Block available? ---- YES ----> Read from Memory | NO | v Read Block from Datafile | v Place Block into Buffer Cache | v Return Data
If the required block is already in memory, Oracle can avoid a physical disk read.
Logical Read vs Physical Read
This is an important concept for Oracle DBAs.
Logical Read — A logical read occurs when Oracle reads a block from the buffer cache.
Buffer Cache → Oracle Session
Physical Read — A physical read occurs when Oracle has to read the block from a datafile because the required block is not available in the buffer cache.
Datafile → Buffer Cache → Oracle Session
Reducing unnecessary physical I/O is an important part of database performance tuning.
Buffer Cache Block States
Database blocks in the buffer cache can be in different states.
1. Free Buffer
A free buffer is available to hold a new database block.
FREE | v Can be reused
Oracle can use this buffer when a new block needs to be loaded into memory.
2. Clean Buffer
A clean buffer contains a copy of a database block that has not been modified since it was read from the datafile.
Example:
Datafile Block
|
v
Buffer Cache
|
CLEANThe contents in memory are consistent with the corresponding block in the datafile.
3. Dirty Buffer
A dirty buffer contains a database block that has been modified in memory but the modified block has not yet been written to the datafile.
Example:
Datafile | v Buffer Cache | v UPDATE | v DIRTY BUFFER
For example:
UPDATE employees SET salary = salary + 500 WHERE employee_id = 101;
Oracle modifies the block in the buffer cache. The corresponding datafile block may still contain the old value. That buffer is therefore called a dirty buffer. Later, DBWn writes the modified block to the datafile.
4. Pinned Buffer
A pinned buffer is a buffer that is currently being accessed and cannot be immediately reused.
Buffer | v Currently in use | v PINNED
This prevents Oracle from replacing a buffer while it is actively being used.
Database Buffer Cache — Write Process
A common misconception is:
Oracle normally changes the block in the buffer cache first. For example:
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;
The simplified flow is:
UPDATE | v Find block | v Buffer Cache | v Modify block | v Dirty Buffer | v DBWn | v Datafile
The DBWn process writes dirty buffers to the datafiles when required by Oracle's internal write conditions.
Buffer Cache and LRU
Oracle uses algorithms to manage the buffer cache and determine which buffers are suitable for reuse.
Historically, the concept is commonly explained using LRU — Least Recently Used.
Modern Oracle versions use more sophisticated buffer management algorithms, so LRU is best understood as a conceptual model rather than the complete implementation.
Buffer Cache and Full Table Scan
Suppose a table is larger than the available buffer cache. Oracle may need to read many blocks from the datafiles.
Large Table
|
v
Datafiles
|
v
Physical Reads
|
v
Buffer CacheWhether a full table scan is efficient depends on many factors, including table size, selectivity, storage performance, optimizer decisions, and caching behavior.
Useful Buffer Cache Views
Oracle DBAs can use dynamic performance views to investigate buffer cache activity.
SELECT name, block_size, current_size, buffers FROM v$buffer_pool;
You can also check instance-level statistics:
SELECT name, value
FROM v$sysstat
WHERE name IN (
'db block gets',
'consistent gets',
'physical reads'
);These statistics can help in understanding logical and physical I/O activity.
13. Redo Log Buffer
The Redo Log Buffer is an SGA memory structure that temporarily stores redo entries generated by database changes.
Redo is one of the most important concepts in Oracle because it allows Oracle to recover database changes after failures.
SQL Operation
|
+---------+---------+
| | |
INSERT UPDATE DELETE
| | |
+---------+---------+
|
v
REDO ENTRY
|
v
REDO LOG BUFFER
|
v
LGWR
|
v
ONLINE REDO LOG FILES
What is Redo?
Redo is information that Oracle generates to describe changes made to the database. For example:
UPDATE employees SET salary = 50000 WHERE employee_id = 101;
Oracle generates redo information associated with this change. The redo information helps Oracle reproduce changes during recovery.
Redo Entries
Changes generated by database operations are recorded as redo entries.
Common operations that generate redo include:
- INSERT
- UPDATE
- DELETE
- DDL operations
- Index modifications
- Other database changes
Redo is generated even when the corresponding database block is currently in the buffer cache.
Redo Log Buffer Flow
The simplified architecture is:
User Process
|
v
Server Process
|
v
Database Change
|
+--------+--------+
| |
v v
Buffer Cache Redo Entry
| |
v v
Dirty Buffer Redo Log Buffer
| |
| v
| LGWR
| |
| v
| Online Redo Log
|
v
DBWn
|
v
Datafile
Redo Log Buffer and COMMIT
One of the most important DBA concepts is what happens when a transaction issues:
COMMIT;
Oracle must make the transaction's redo durable according to the configured commit behavior.
The simplified flow is:
DML | v Redo generated | v Redo Log Buffer | v COMMIT | v LGWR writes redo | v Online Redo Log | v COMMIT acknowledged
Why Doesn't LGWR Wait for DBWn?
This is a very common Oracle DBA interview question. Suppose:
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101; COMMIT;
The changed data block may still be in the buffer cache. Oracle does not need to wait for DBWn to write that data block to the datafile before completing the normal commit. Instead, LGWR writes the required redo to the online redo log.
COMMIT
|
v
Redo Log Buffer
|
v
LGWR
|
v
Online Redo Log
|
v
COMMIT ACKLater:
Dirty Buffer
|
v
DBWn
|
v
DatafileThis design allows Oracle to provide transaction durability without requiring every changed data block to be immediately written to the datafile.
Write-Ahead Logging Principle
Oracle follows a fundamental recovery principle commonly described as write-ahead logging.
This is one of the key reasons Oracle can recover committed transactions after an instance failure.
LGWR — Log Writer
LGWR is the Oracle background process responsible for writing redo entries from the redo log buffer to the online redo log files.
LGWR can write redo under several circumstances, including:
- When a transaction commits
- When the redo buffer needs to be flushed
- Periodically
- Before DBWn writes certain modified blocks to datafiles
- During other internal database operations
Simplified flow:
Redo Log Buffer
|
v
LGWR
|
v
Online Redo Log GroupRedo Log Buffer vs Database Buffer Cache
These two memory areas are often confused.
| Database Buffer Cache | Redo Log Buffer |
|---|---|
| Stores database blocks | Stores redo entries |
| Contains copies of data blocks | Contains information describing changes |
| Used for reading and modifying database blocks | Used for recovery information |
| DBWn eventually writes dirty blocks to datafiles | LGWR writes redo to online redo logs |
| Part of SGA | Part of SGA |
DBWn vs LGWR
This is one of the most important Oracle DBA interview questions.
| DBWn | LGWR |
|---|---|
| Database Writer | Log Writer |
| Writes dirty buffers | Writes redo entries |
| Writes to datafiles | Writes to online redo logs |
| Works with Database Buffer Cache | Works with Redo Log Buffer |
| Writes database blocks | Writes recovery information |
ORACLE SGA
|
+---------+---------+
| |
v v
Buffer Cache Redo Log Buffer
| |
v v
DBWn LGWR
| |
v v
Datafiles Online Redo LogsImportant Interview Scenario
Question
A user executes:
UPDATE employees SET salary = 60000 WHERE employee_id = 101; COMMIT;
What happens internally?
Simplified Answer
1. Oracle finds the required block.
|
v
2. Block is loaded into Buffer Cache if required.
|
v
3. Oracle modifies the block in memory.
|
v
4. The block becomes DIRTY.
|
v
5. Oracle generates REDO.
|
v
6. Redo is placed in Redo Log Buffer.
|
v
7. User issues COMMIT.
|
v
8. LGWR writes required redo to Online Redo Log.
|
v
9. COMMIT is acknowledged.
|
v
10. DBWn later writes the dirty block to Datafile.This is the basic relationship between: Buffer Cache + Redo Log Buffer + LGWR + DBWn + Datafiles + Online Redo Logs.
Easy Way to Remember Oracle Memory
SGA | +-- Shared Pool | | | +-- Library Cache | +-- Data Dictionary Cache | +-- Result Cache | +-- Database Buffer Cache | | | +-- Data Blocks | +-- Clean Buffers | +-- Dirty Buffers | +-- Redo Log Buffer | | | +-- Redo Entries | +-- Large Pool | +-- Java Pool | +-- Streams Pool
And the two most important write paths are:
DATA PATH
=========
Buffer Cache
|
v
DBWn
|
v
Datafiles
REDO PATH
=========
Redo Log Buffer
|
v
LGWR
|
v
Online Redo Logs- DBWn protects the database blocks by writing modified buffers to datafiles.
- LGWR protects transaction durability by writing redo information to online redo logs.