DB_WRITER_PROCESSES - Tune Oracle DBWR for Write Performance

DB_WRITER_PROCESSES

Overview

DB_WRITER_PROCESSES controls the number of Database Writer (DBWn) background processes Oracle starts at instance startup. DBWn processes are responsible for writing dirty (modified) buffers from the buffer cache to disk — a task that directly affects checkpoint performance, I/O throughput, and the frequency of free buffer waits. On systems with fast storage (SSDs, NVMe, or SAN arrays) and high buffer cache churn, a single DBWn process can become a bottleneck. Adding more DBWn processes allows Oracle to parallelize write operations and keep pace with the workload.

Each DBWn process is named DBW0 through DBW9, then DBWa through DBWj for values above 10. Multiple DBWn processes write concurrently from different parts of the buffer cache, improving throughput without coordination overhead. On systems using asynchronous I/O (DISK_ASYNCH_IO=TRUE), a single DBWn can already overlap many I/O requests, so the benefit of multiple DBWn processes is greatest when async I/O is not available or when write volumes are very high.

Parameter Type: Static (requires instance restart to change) Default Value: 1 (or CPU_COUNT/8, whichever is greater, on some platforms) Valid Range: 1 to 100 Available Since: Oracle 8i Modifiable: No — SCOPE=SPFILE only; takes effect on next startup PDB Modifiable: No (CDB-level setting)

Configuration

Viewing Current Value

-- Check current DB_WRITER_PROCESSES setting
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'db_writer_processes';

-- Check SPFILE value
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'db_writer_processes';

-- Verify how many DBWn processes are actually running
SELECT name, program, background
FROM v$process
WHERE program LIKE '%DBW%'
ORDER BY name;

-- Check CPU count for sizing reference
SELECT name, value
FROM v$parameter
WHERE name IN ('db_writer_processes', 'cpu_count')
ORDER BY name;

Setting the Parameter

-- Set to 4 DBWn processes (common for medium workloads)
ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;

-- Set to 8 DBWn processes (for high-write OLTP or large buffer caches)
ALTER SYSTEM SET db_writer_processes = 8 SCOPE=SPFILE;

-- Reset to default (1 process)
ALTER SYSTEM SET db_writer_processes = 1 SCOPE=SPFILE;

-- After changing, restart the instance to apply
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- Confirm running DBWn count after restart
SELECT COUNT(*) AS dbwn_count
FROM v$process
WHERE program LIKE '%DBW%';

Tuning Guidance

Recommended Values

Environment	Recommended DB_WRITER_PROCESSES
Small DB (< 8 CPUs, SSD storage)	1–2
Medium DB (8–16 CPUs, SSD/SAN)	2–4
Large OLTP (16–32 CPUs, high write rate)	4–8
Very large DB (32+ CPUs, NVMe/SAN)	8–16
RAC node (per instance)	2–4 per node
HDD storage (low IOPS ceiling)	1–2 (I/O subsystem is the bottleneck, not DBWn)
Data warehouse (low write, high read)	1–2

For most modern systems with SSDs or SAN storage and async I/O enabled, 1–4 DBWn processes is sufficient. Only increase beyond 4 when you observe consistent free buffer waits or write complete waits that point to DBWn saturation.

How to Size

The primary indicator that you need more DBWn processes is high free buffer waits — sessions waiting for DBWn to flush dirty buffers to make room in the cache.

-- Check for free buffer waits (key DBWn saturation indicator)
SELECT event, total_waits, time_waited,
       ROUND(time_waited / NULLIF(total_waits, 0), 2) AS avg_wait_centisecs
FROM v$system_event
WHERE event IN (
  'free buffer waits',
  'write complete waits',
  'checkpoint completed'
)
ORDER BY time_waited DESC;

-- Review DBWn write statistics per process
SELECT name, value
FROM v$sysstat
WHERE name IN (
  'DBWR checkpoint buffers written',
  'DBWR thread checkpoint buffers written',
  'DBWR transaction table writes',
  'DBWR undo block writes',
  'DBWR revisited being-written buffer',
  'DBWR buffers scanned',
  'DBWR lru scans'
)
ORDER BY name;

-- Identify checkpoint performance
SELECT checkpoint_change#,
       checkpoint_time,
       resetlogs_time
FROM v$database;

Sizing Formula

Starting point:
  DB_WRITER_PROCESSES = MAX(1, FLOOR(CPU_COUNT / 8))

For high-write OLTP:
  DB_WRITER_PROCESSES = MAX(2, FLOOR(CPU_COUNT / 4))

Never exceed:
  DB_WRITER_PROCESSES > CPU_COUNT / 2
  (More DBWn than half the CPUs rarely provides additional benefit)

Monitoring

-- Monitor buffer cache pressure over time
SELECT name, value
FROM v$sysstat
WHERE name IN (
  'free buffer requested',
  'free buffer inspected',
  'dirty buffers inspected',
  'physical writes'
)
ORDER BY name;

-- Check if checkpoint is keeping up (high gap = DBWn pressure)
SELECT log_mode,
       checkpoint_change#,
       current_scn,
       current_scn - checkpoint_change# AS checkpoint_gap
FROM v$database;

-- Review I/O throughput by datafile to find write hot spots
SELECT f.name,
       s.phywrts,
       s.writetim,
       ROUND(s.writetim / NULLIF(s.phywrts, 0), 2) AS avg_write_ms
FROM v$datafile f
JOIN v$filestat s ON f.file# = s.file#
WHERE s.phywrts > 0
ORDER BY s.writetim DESC
FETCH FIRST 10 ROWS ONLY;

Common Issues

Issue 1: Free Buffer Waits Despite Multiple DBWn Processes

If free buffer waits remain high after increasing DB_WRITER_PROCESSES, the bottleneck may be the I/O subsystem rather than the number of writer processes. More DBWn processes cannot write faster than the underlying storage allows.

Resolution: Profile the storage I/O throughput and latency. If the storage is the bottleneck, consider upgrading to faster storage (SSD/NVMe), optimizing I/O paths, or distributing datafiles across more storage devices.

-- Check if I/O latency is the constraint
SELECT f.name,
       s.writetim / NULLIF(s.phywrts, 0) * 10 AS avg_write_ms
FROM v$datafile f
JOIN v$filestat s ON f.file# = s.file#
WHERE s.phywrts > 100
ORDER BY avg_write_ms DESC;
-- Values above 20ms on a modern SAN or SSD suggest a storage bottleneck

Issue 2: Too Many DBWn Processes Causing CPU Overhead

Setting DB_WRITER_PROCESSES excessively high (e.g., 32 on an 8-CPU system) creates process overhead without proportional I/O improvement. Each DBWn process consumes memory and CPU time for scanning the LRU list and managing write batches.

Resolution: Reduce DB_WRITER_PROCESSES to a value proportional to CPU count. Monitor free buffer waits to confirm the reduced count remains sufficient.

-- Verify CPU utilization by DBWn processes using OS-level tools (e.g., top, ps)
-- Within Oracle, confirm no free buffer waits after reduction
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event = 'free buffer waits';

Issue 3: Parameter Has No Effect Without Restart

Because DB_WRITER_PROCESSES is a static parameter, changing it with SCOPE=BOTH will fail. You must use SCOPE=SPFILE and restart the instance.

Resolution: Always use SCOPE=SPFILE when modifying this parameter, then schedule a maintenance window for the restart.

-- Incorrect (will raise ORA-02095: specified initialization parameter cannot be modified)
-- ALTER SYSTEM SET db_writer_processes = 4 SCOPE=BOTH;

-- Correct
ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;
-- Then: SHUTDOWN IMMEDIATE; STARTUP;

Related Parameters

• DISK_ASYNCH_IO — Enables async I/O; when TRUE, a single DBWn can handle more concurrent writes, reducing the need for multiple DBWn processes
• FILESYSTEMIO_OPTIONS — Configures direct and async I/O at the file system level; affects overall write throughput
• DB_FILE_MULTIBLOCK_READ_COUNT — Read-side I/O parameter; DBWn writes are separate but part of the same I/O subsystem
• SGA_TARGET — Larger buffer caches increase the volume of dirty buffers DBWn must manage

Related Errors

• ORA-01652: Unable to Extend Temp Segment — Indirectly related; a slow checkpoint (DBWn bottleneck) can delay space reclamation
• ORA-03113: End-of-file on Communication — Background process failures including DBWn can surface as session disconnections
• ORA-00257: Archiver Error — Checkpoint lag caused by slow DBWn can contribute to redo archiving pressure

Version Notes

Version	Notes
Oracle 8i	Parameter introduced; single DBWn was standard
Oracle 10g	Multiple DBWn processes supported; Oracle began auto-calculating default based on CPU_COUNT
Oracle 11g	Up to 20 DBWn processes supported; async I/O made multiple processes less critical on modern storage
Oracle 12c+	Range extended to 100; still rarely needed above 8–10 in practice
Oracle 19c+	No functional changes; Oracle recommends starting with default and increasing only when free buffer waits are observed
Oracle 21c / 23ai	Behavior unchanged; default calculation based on CPU_COUNT remains the recommended starting point