The database block writer (DBWn) is the background process responsible for writing dirty blocks to disk. DBWn will write dirty blocks from the buffer cache, usually to make more room in the cache (to free buffers for reads of other data) or to advance a checkpoint (to move forward the position in an online redo log file from which Oracle would have to start reading, to recover the instance in the event of failure).
As we discussed in Chapter 3, when Oracle switches log files, a checkpoint is signaled. Oracle needs to advance the checkpoint so that it no longer needs the online redo log file it just filled up. If it hasn’t been able to do that by the time we need to reuse that redo log file, we get the “checkpoint not complete” message and we must wait.
Note Advancing log files is only one of many ways for a checkpoint activity to occur. There are incremental checkpoints controlled by parameters such as FAST_START_MTTR_TARGET and other triggers that cause dirty blocks to be flushed to disk.
As you can see, the performance of DBWn can be crucial. If it does not write out blocks fast enough to free buffers (buffers that can be reused to cache some other blocks) for us, we will see both the number and duration of waits on Free Buffer Waits and Write Complete Waits start to grow.
We can configure more than one DBWn; in fact, with later versions of Oracle, we can configure as many as 100, as evidenced by the following query:
SQL> select name, description from v$bgprocess where description like ‘db writer process%’;
NAME DESCRIPTION
DBW0 db writer process 0 DBW1 db writer process 1 DBW2 db writer process 2
…
BW97 db writer process 97
BW98 db writer process 98
BW99 db writer process 99
100 rows selected.
Most systems run with one database block writer, but larger, multi-CPU systems can make use of more than one. This is generally done to distribute the workload of keeping a large block buffer cache in the SGA clean, flushing the dirtied (modified) blocks to disk.
Optimally, the DBWn uses asynchronous I/O to write blocks to disk. With asynchronous I/O, DBWn gathers up a batch of blocks to be written and gives them to the operating system. DBWn does not wait for the operating system to actually write the blocks out; rather, it goes back and collects the next batch to be written.
As the operating system completes the writes, it asynchronously notifies DBWn that it completed the writes. This allows DBWn to work much faster than if it had to do everything serially. We’ll see later in the “Slave Processes” section how we can use I/O slaves to simulate asynchronous I/O on platforms or configurations that do not support it.
I would like to make one final point about DBWn. It will, almost by definition, write out blocks scattered all over disk—DBWn does lots of scattered writes.
When you do an update, you’ll be modifying index blocks that are stored here and there and data blocks that are also randomly distributed on disk. LGWR, on the other hand, does lots of sequential writes to the redo log.
This is an important distinction and one of the reasons that Oracle has a redo log and the LGWR process as well as the DBWn process. Scattered writes are significantly slower than sequential writes. By having the SGA buffer dirty blocks and the LGWR process do large sequential writes that can re-create these dirty buffers, we achieve an increase in performance.
The fact that DBWn does its slow job in the background while LGWR does its faster job while the user waits gives us better overall performance.
This is true even though Oracle may technically be doing more I/O than it needs to (writes to the log and to the datafile); the writes to the online redo log could in theory be skipped if, during a commit, Oracle physically wrote the modified blocks out to disk instead.
In practice, it does not happen this way. LGWR writes the redo information to the online redo logs for every transaction, and DBWn flushes the database blocks to disk in the background.