Spotlight on Oracle 10.4 - Getting Started Guide

Improve 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 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. Improve Database I/O

Tip: The Spotlight on Oracle Release Notes document may also provide solutions for specific Oracle issues that are related to asynchronous I/O on individual platforms.