Oracle Add Database Writer Processes

The database writer process (often referred to as DBWR from the times when only one was allowed, but more properly referred to as DBWn) is responsible for asynchronously writing out dirty buffers in the database buffer cache to datafiles, and for managing the least recently used (LRU) lists. In Oracle 7 Release 7.3, the DBWR process was responsible for writing checkpoint information to the control files and the headers of all the datafiles (from Oracle 8 onwards this functionality is performed by the Checkpoint process). The default Oracle configuration starts a single database writer process, which can be a bottleneck for performing I/O in heavy I/O systems.

One way of eliminating this bottleneck is to create more database writers. The ability to add more database writer slaves was introduced in Oracle 7 as a way of simulating asynchronous I/O on platforms that didn't support this functionality. (As an aside, using asynchronous I/O is generally preferred more than simulating it in this manner.) In Oracle 7 release 7.3, you could have up to 50 database writer slaves by using the DB_WRITERS parameter. If ASYNC_WRITE was set to TRUE, the DB_WRITERS parameter was ignored and an error message written to the alert log:

WARNING: db_writers = 10 in init.ora file ignored, since asynchronous I/O has been
requested(async_write = TRUE)

In Oracle 8 release 8.0, multiple database writer processes (as opposed to database writer slaves in Oracle 7 release 7.3) were introduced. The parameter name was changed to DB_WRITER_PROCESSES, and the maximum number was limited to 10 (this changed to 20 with release 9.2). With multiple CPU systems, having additional database writer processes can significantly enhance the performance of writing dirty buffers to disk, as well as reducing contention for the cache buffer LRU chain latch. Multiple database writer processes could be disabled either by setting the parameter DBWR_IO_SLAVES to a non-zero value or by setting the underscore parameter _DB_BLOCK_CACHE_PROTECT to TRUE. DBWR_IO_SLAVES and DB_WRITER_PROCESSES are mutually exclusive – you can only have one or the other. If DBWR_IO_SLAVES is set to a non-zero value, the DB_WRITER_PROCESSES parameter is ignored. DBWR_IO_SLAVES also requires the parameter DISK_ASYNC_IO to be turned off (by default, this parameter is turned on). If DISK_ASYNC_IO is set to TRUE and DBWR_IO_SLAVES is set to a non-zero value, the following error message is written to the alert log:

Cannot start multiple dbwrs when using I/O slaves

This is a slightly misleading error message, since it doesn't really tell you which of the parameters DBWR_IO_SLAVES or DB_WRITER_PROCESSES is the culprit.