When Does the Database Writer (DBWn) Write?

DBWn writes dirty buffers from the buffer cache to datafiles. It is designed for throughput and cache health, not commit durability. LGWR makes COMMITs durable by flushing redo; DBWn can lag safely while honoring write-ahead logging.

The three primary triggers

1. Free buffer pressure — When server processes cannot find a clean reusable buffer after scanning a threshold, they signal DBWn to write dirty buffers and replenish the free lists.
2. Checkpoint progress — To advance the checkpoint SCN, DBWn writes dirty buffers so that file headers (updated by CKPT) reflect a more recent recovery point. This reduces MTTR.
3. Dirty list thresholds — When the number of dirty blocks crosses internal targets, DBWn drains queues to keep aging and write volume under control.

Other occasions DBWn writes

• File/tablespace state changes: online/offline, read-only transitions, or DDL boundaries that require flushing.
• Aging/time-based sweeps: to prevent very old dirty buffers from lingering indefinitely.
• Shutdown normal/immediate: instance shutdown drives checkpointing and associated DBWn activity.

Multiple Writers and I/O behavior

• Busy systems may run multiple writers (DBW0, DBW1, …) to spread random I/O. Prefer asynchronous I/O at the OS/storage layer.
• DBWn uses multi-block writes where possible, but writes are typically scattered compared to LGWR’s sequential pattern.

Monitoring: quick, actionable queries

-- Who are the DBWn processes?
SELECT name, description
FROM   v$bgprocess
WHERE  name LIKE 'DBW%';

-- Write and checkpoint activity since startup
SELECT name, value
FROM   v$sysstat
WHERE  name IN ('physical writes', 'physical writes direct', 'DBWR checkpoints');

-- MTTR targeting and current estimate
SHOW PARAMETER fast_start_mttr_target;
SELECT estimated_mttr
FROM   v$instance_recovery;

-- Which datafiles experience the most I/O?
SELECT filetype_name,
       small_read_megabytes + large_read_megabytes AS read_mb,
       small_write_megabytes + large_write_megabytes AS write_mb
FROM   v$iostat_file
ORDER  BY (read_mb + write_mb) DESC;

Troubleshooting Symptoms and likely causes

• High free buffer waits / buffer busy waits: frequent free-list depletion → DBWn pressure; examine hot objects, read patterns, buffer cache sizing (ASMM/AMM), and write throughput.
• Long checkpoints / high recovery time: adjust FAST_START_MTTR_TARGET to meet RTO; verify with V$INSTANCE_RECOVERY; ensure redo logs are sized to avoid incessant switches.
• Write stalls: confirm async I/O; review storage queue depth, latency, and contention on busy datafiles/ASM disks.

Configuration guidance

1. Right-size memory: Use ASMM/AMM to balance buffer cache vs. shared pool; validate with workload evidence, not a single ratio.
2. Align with MTTR: Set FAST_START_MTTR_TARGET to business RTO; DBWn/CKPT patterns will adapt accordingly.
3. Consider multiple DBWn only when needed: Add writers after confirming sustained dirty-buffer pressure and I/O capacity to benefit.
4. Favor async I/O over I/O slaves: Modern platforms handle concurrency better with true asynchronous writes.

Key takeaway

DBWn writes are driven by the need to keep the cache healthy and recovery fast, not by COMMITs. Focus on free-buffer pressure, checkpoint objectives, and storage throughput; verify improvements using the V$ views shown above.

Database Writer - Quiz