Oracle and Direct I/O
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 STATSPACK
stats$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.
SEE CODE DEPOT FOR FULL SCRIPTS
