Spotlight on Oracle – Improving Database Writer Performance

Improving database writer performance

Free buffer or write complete waits can indicate that the database writer process (DBWR) process is experiencing a disk I/O bottleneck.

The DBWR is the only process that writes modified database blocks from the buffer cache to the database files. The database writer writes asynchronously. This means that a user process never needs to wait for the database writer to complete. If, however, the DBWR falls behind sufficiently, the buffer cache fills up with dirty blocks and waits occur while user processes try to introduce new blocks into the cache.

Keeping the database writer optimized is therefore critical to maintaining database throughput. The best way to optimize throughput is to spread I/O across multiple disk devices, and allow the database writer to write to these disk devices in parallel. This can be achieved in two ways:

• Multiple database writers can be configured using the DB_WRITERS (v7) or DBWR_IO_SLAVES configuration parameter.
• Operating system asynchronous I/O or list I/O can be enabled. This allows the database writer to issue write requests against multiple disk devices simultaneously.

Experience shows that operating system asynchronous I/O performs more efficiently than multiple database writers. However, asynchronous I/O may not be available on all platforms, or may require special measures. In some operating systems, asynchronous I/O may require that the database be built on raw devices.

If you are configuring multiple database writers, you may benefit from configuring as many database writers as you have physical disks.