Oracle and Direct I/O

From http://www.rampant-books.com/t_oracle_direct_i_o.htm

Many Oracle shops are plagued with slow I/O intensive databases, and this tip is for anyone whose top 5 timed events shows disk I/O as a major event:

 

Top 5 Timed Events

                                                 % Total

Event                         Waits             Ela Time

--------------------------- ------------ ----------- -----------

db file sequential read       2,598               48.54

db file scattered read       25,519               22.04

library cache load lock         673                9.26

CPU time                      2,154                7.83

log file parallel write      19,157                5.68

 

This tip is important if there are reads waits in the top-5 timed events.  If disk I/O is not the bottleneck, then making it faster will not improve performance.

 

Direct I/O is an OS-level solution, and often I/O-bound Oracle databases can be fixed by tuning the SQL to reduce unnecessary large-table full-table scans.  File I/O can be monitored using the AWR dba_hist_filestatxs table or the STATSPACKstats$filestatxs table.

 

For optimal disk performance, Oracle should always use direct I/O to its data files, bypassing any caching at the OS layer.  Direct I/O must be enabled both in Oracle and in the operating system.

 

Oracle controls direct I/O with a parameter named filesystemio_options . According to the Oracle documentation, the filesystemio_options parameter must be set to SETALL or DIRECTIO in order for Oracle to read data blocks directly from disk

 

Using DIRECTIO allows the enhancement of I/O through the bypassing of the redundant OS block buffers, reading the data block directly into the Oracle SGA.  Using direct I/O also allows the creation of multiple blocksized tablespaces.  improve I/O performance. 

 

Using filesystemio_options=SETALL allows both asynchronous I/O and direct I/O, the preferred method according to the Oracle 11g documentation.

Checking the Server Direct I/O Option

Methods for configuring the OS will vary depending on the operating system and file system in use. The following list contains some examples of quick checks that anyone can perform to ensure that direct I/O is in use:

    Enabling Direct I/O with Kernel Parameters

Oracle recommends that all database files use Direct I/O, which is a disk access method that bypasses the additional overhead on the OS buffer.  One important exception to this rule is the archived redo log filesystem which should use OS buffer caching.  The following information details the method for ensuring that the OS uses Direct I/O:

Direct I/O for Windows

The Windows OS requires no special configuration to ensure that the database files use Direct I/O.

Direct I/O for IBM AIX

The filesystemio_options initialization parameter should be set to configure a database to use either direct I/O or concurrent I/O when accessing datafiles, depending on the file system that is used to store them.

 

Specification of the value SETALL for this parameter ensures that:

§       Datafiles on a JFS file system are accessed using direct I/O

§       Datafiles on a JFS2 file system are accessed using concurrent I/O

In Oracle 9i Server Release 2, filesystemio_options was a hidden parameter. Starting with 10g, the parameter is externally available.

 

filesystemio_options can be set to any of the following values:

§    ASYNCH: Set by default. This allows asynchronous I/O to be used where supported by the OS.

§      DIRECTIO: This allows direct I/O to be used where supported by the OS. Direct I/O bypasses any Unix buffer cache.

§       SETALL : Enables both ASYNC and DIRECT I/O.

§      NONE: This disables ASYNC I/O and DIRECT I/O so that Oracle uses normal synchronous writes, without any direct I/O options.

Direct I/O for Linux

Direct I/O support is not available and is not supported on Red Hat Enterprise Linux 2.1 and UnitedLinux.  It is available and is supported on Red Hat Enterprise Linux 3 also over NFS, if the driver being used on the system supports varyio.   To enable direct I/O support:

§       Set the filesystemio_options parameter in the parameter file to DIRECTIO (filesystemio_options = DIRECTIO)

§       If the asynchronous I/O option is in use, the filesystemio_options parameter in the parameter file should be set to SETALL.

Also with 10g, this feature is already working which means that is does not require any patch.  For Oracle9i, the DBA will need to download <patch:2448994> - Abstract: DIRECT IO SUPPORT OVER NFS

Direct I/O for Sun Solaris

The focus should be the FORCEDIRECTIO option.  Oracle DBAs claim this option makes a tremendous difference in I/O speed for Sun servers. 

Direct I/O for Veritas

For Veritas VxFS, including HP-UX, Solaris, and AIX Veritas, for the following setting should be used: convosync=direct.  It is also possible to enable direct I/O on a per file basis using Veritas QIO; refer to the QIOSTAT command and corresponding manual page for hints.