Oracle Performance Tuning Tips

Use asynchronous I/O

The default value of the disk_asynch_io parameter is TRUE. This means that Oracle will attempt to use asynchronous I/O if possible. Asynchronous I/O is important to DBWn to allow it to make effective use of the hardware's I/O bandwidth. LGWR also needs asynchronous I/O to parallelize writes to multiple log file members and to overlap redo writes when transactions are committed in quick succession. Asynchronous I/O is important to foreground processes so that they can prefetch blocks for full table scans and fast full index scans before they are required. Foreground processes also use asynchronous I/O for direct reads and direct writes, normally to temporary segments. This enables the I/O to be done in parallel with other, CPU intensive work such as comparisons for sorts and hash joins.

The availability of asynchronous I/O to log files and tempfiles is much less problematic than the availability of asynchronous I/O to datafiles. This is because log files and tempfiles do not need to be backed up. So there is no impediment to using raw log files and tempfiles, and several other compelling reason for doing so (see here and here). As a consequence of using raw log files and tempfiles, the most efficient form of asynchronous I/O, namely kernelized asynchronous I/O, is almost always available for I/O to these files. However, the availability of asynchronous I/O to datafiles is a more taxing question.

The availability of asynchronous I/O to datafiles affects DBWn's ability to make use of the hardware I/O bandwidth, and it affects the the prefetching of data for sequential reads by foregrounds. Prefetching has an obvious performance impact that is particularly important in environments like data warehouses. However, because DBWn does its work in the background, the benefit of asynchronous I/O for DBWn is better scalability, not better performance as such. That is, asynchronous I/O allows DBWn to work more efficiently and thus manage higher workloads without foreground processes needing to wait for its services.

If the datafiles are file system based, Oracle's attempts to use asynchronous I/O to the datafiles will result in the use of threaded asynchronous I/O -- but only if the operating system supports threaded asynchronous I/O to file system files, and assuming that the values of the disk_asynch_io and _filesystemio_options parameters have not been changed from their defaults to disable asynchronous I/O. The performance of threaded asynchronous I/O to datafiles is acceptable in many environments. It enables asynchronous prefetching, and does not result in a proliferation of I/O threads unless the DBWn workload is moderately intensive.

If DBWn workload is an issue, the maximum number of asynchronous I/O threads used by the DBWn processes can be limited with the _db_writer_max_writes parameter. Alternatively, asynchronous I/O can be disabled for DBWn only using the _dbwr_async_io parameter, and explicit I/O slave processes can be configured instead using the dbwr_io_slaves parameter. These options are preferable to disabling asynchronous I/O entirely using disk_asynch_io or just threaded asynchronous I/O using _filesystemio_options, because the ability to use asynchronous I/O against raw files and for asynchronous prefetching from filesystem based datafiles is preserved. Unfortunately, the physical write bandwidth may not be fully used if DBWn's write bandwidth has to be constrained.

Of course, for performance critical databases constraining the write bandwidth in this way to avoid wasting CPU time is unacceptable. Instead, raw datafiles or Quick I/O must be used to enable the use of efficient kernelized asynchronous I/O.

Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home