Oracle Enable Direct and Asynchronous I/O

Enabling Direct I/O

Oracle provides a number of different ways of ensuring the performance of I/O write requests is optimal, including the use of direct I/O and asynchronous I/O (see below for more details on asynchronous I/O). Direct I/O bypasses the operating system buffer cache and writes directly to disk. For systems that have their own buffer cache like Oracle, this can enhance performance significantly. Whether direct I/O is available or not is dependent on the operating system itself, as well as the type of filesystem in use for that operating system (most operating systems nowadays support multiple different types of filesystems).

Enabling Asynchronous I/O

When an I/O write request is submitted to the operating system, the process performing the write can do so in either a synchronous or asynchronous manner. Synchronous I/O causes the process performing the write to be blocked until the write is finished before it can continue. Asynchronous I/O, on the other hand, allows the process performing the write to submit the write request and then continue without waiting for confirmation that the write is complete. The capability to perform asynchronous I/O at the operating system level (as opposed to the asynchronous writing nature of the DBWn process) is platform dependent. Some support asynchronous I/O by default, while others might only support it for raw devices or require additional configuration to allow asynchronous I/O.

As highlighted elsewhere in this section, Oracle provided two different methods before Oracle 9iR2 to help increase DBWn output using asynchronous I/O. You can only use one of these features on any one platform, depending on your hardware, the version of Oracle, the operating system level, and the OS kernel patches you currently have loaded. Consult your Hardware Installation Guide for more details.

• DBWR_IO_SLAVES: (Prior to Version 8.0, this was named DB_WRITERS) Number of DBWR slave processes started. If this parameter is used, set it to the number of disk drives used by the Oracle datafiles. Use of this parameter will result in an increased need for system semaphores and an increase in the processes parameter.

• DISK_ASYNCH_IO (Prior to Release 8.0, this was named ASYNCH_WRITE): Permits a program to continue executing without having to wait for an I/O write to complete. The DBWR can continue writing modified database blocks out to disk without having to wait for each I/O operation to finish. You should use asynchronous I/O if it is available on your platform.

Oracle 9iR2 introduced another parameter, FILESYSTEMIO_OPTIONS, to enable or disable direct I/O and asynchronous I/O for files on filesystems (the parameter was actually introduced in 8.1.7 as an underscore parameter, and externalized in the 9.2 release). The values this parameter can take are:

• NONE – disable direct and asynchronous I/O for files on filesystems
• ASYNCH – enable only asynchronous I/O for files on filesystems
• DIRECTIO – enable only direct I/O for files on filesystems
• SETALL – enable both direct I/O and asynchronous I/O for files on filesystems

The default value of this parameter is platform-specific. It can be dynamically changed with an ALTER SYSTEM command if necessary. It may be beneficial to use this parameter if your database uses raw devices for tempfiles and redo log files, but not for datafiles.

The performance of asynchronous I/O is operating system dependent. There are two different types of asynchronous I/O, kernelized asynchronous I/O (commonly known as KAIO) and threaded asynchronous I/O. Threaded asynchronous I/O uses an alternative code path to KAIO to simulate asynchronous I/O for files on filesystems, and is generally not beneficial for Oracle use.

One downside of KAIO is that is only available for raw devices and Quick I/O files. It is called kernelized asynchronous I/O because support for the asynchronous I/O is specifically built into the kernel using device drivers, rather than simulated like the threaded asynchronous I/O implementation. KAIO is currently available for Solaris, HP-UX, AIX, and Tru64, but is not available for Windows or Linux (Linux support is expected with the 2.5 kernel release).