Lesson 8	Database Writer (DBW0)
Objective	Identify the purpose of the DBW0 process.

Database Writer (DBWn): Dirty Buffer Flushes, Checkpoints, and Throughput

DBWn (Database Writer) is the Oracle background process family that writes dirty buffers from the buffer cache to datafiles. DBWn is about throughput and cache health: it frees frames for new blocks and moves modified data to disk in an orderly, checkpoint-aware way. Durability of COMMITs comes from LGWR flushing redo; DBWn can safely lag behind while still honoring write-ahead logging.

How DBWn fits with other processes

LGWR makes transactions durable by flushing redo at commit and at short intervals.
CKPT coordinates checkpoints and updates control/datafile headers; DBWn performs the bulk data block writes.
Server processes modify blocks in memory; DBWn later persists those blocks without blocking user commits.

Buffer lifecycle (visual)

Empty database buffers at startup — Figure 1 - On startup, the buffer cache is empty.

Cache fills with data blocks — Figure 2 - Queries read blocks into the cache.

Data blocks modified — Figure 3 - DML marks buffers dirty; DBWn later writes them to disk.

DBWn writes dirty blocks to disk — Figure 4: DBWn periodically writes dirty buffers to disk.

Modified blocks retained in cache — Figure 5: Written blocks remain in the cache for potential reuse.

DBWn catches up with changes — Figure 6: When activity slows, DBWn ensures all changes are written, keeping the cache up to date.

When does DBWn write?

Checkpoint activity - CKPT signals DBWn to advance the checkpoint; DBWn writes dirty buffers for the target SCN.
Free buffer pressure - If the free list gets low, DBWn writes to replenish clean buffers.
Dirty list thresholds and aging - To keep dirty queues and aged blocks under control.
Tablespace/file state changes - When files go offline/online or other maintenance boundaries require it.

Multiple writers and I/O behavior

Oracle can run multiple DBWn processes (DBW0, DBW1, …) to spread write load on busy or large systems.
Prefer asynchronous I/O at the OS/storage layer. Historical I/O slave settings exist but are rarely needed on modern platforms.

Quick checks and instrumentation

-- See DBWn processes (names vary by platform)
SELECT name, description FROM v$bgprocess WHERE name LIKE 'DBW%';

-- High-level write and checkpoint stats
SELECT name, value FROM v$sysstat
WHERE name IN ('physical writes', 'physical writes direct', 'DBWR checkpoints');

-- Instance recovery targets that influence checkpointing patterns
SHOW PARAMETER fast_start_mttr_target;
SELECT estimated_mttr FROM v$instance_recovery;

Practical guidance

Ensure storage/OS async I/O is enabled; avoid unnecessary I/O slaves.
Right-size buffer cache with ASMM/AMM and validate with workload baselines rather than a single ratio.
Evaluate multiple DBWn only when sustained dirty-buffer pressure exists; measure before/after with AWR/ASH.
Tune checkpointing via workload goals (MTTR), not ad-hoc thresholds; confirm with V$INSTANCE_RECOVERY.

Notes:

Monitor DBWn performance using views like V$SYSSTAT or V$SESSION_EVENT to identify I/O bottlenecks. More on Oracle performance views can be found at Oracle Performance Views