Oracle Direct I/O tips
Oracle Tips by Burleson Consulting
IMPORTANT NOTE: While
most Oracle shops employ direct I/O, there can be compatibility issues when using
direct I/O with a server that also supports async I/O.
Kevin Closson has many great notes on this issue.
Many Oracle shops are plagued with slow I/O intensive databases, and
this tip is for anyone whose STATSPACK top-5 timed events shows disk
I/O as a major event:
Top 5 Timed Events
Waits Time (s)
--------------------------- ------------ ----------- -----------
db file sequential read
db file scattered read 25,519
library cache load lock
log file parallel write 19,157
Oracle direct I/O should be verified
for Solaris, HP/UX, Linux AIX. This tip is important to you if you
have reads waits in your top-5 timed events. Remember, if disk
I/O is not your bottleneck then making it faster WILL NOT improve
Also, this is a 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. I monitor file
I/O using the stats$filestatxs view:
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" (the preferred method, according to the Oracle
documentation) or "directio" in order for Oracle to read data blocks directly
Using direct I/O allows you to
enhance I/O by bypassing the redundant OS block buffers, reading the
data block directly into the Oracle SGA. Using direct I/O also
allow you to create multiple blocksized tablespaces to improve I/O
For information about Oracle direct
I/O, refer to this URL by Steve Adams:
Checking your Server for direct I/O
Methods for configuring the OS will vary depending on the operating
system and file system in use. Here are some examples of
quick checks that anyone can perform to ensure that you are using
10g patch required for
DirectIO on Solaris.
Solaris - Look for a "forcedirectio"
and filesystemio_options=setall option.
Oracle DBAs claim this option makes a huge difference in
I/O speed for Sun servers. Also note this Sun
Solaris document on Oracle: "Avoid buffered VxFS
or Cached QIO". Here is the
Glen Faucett also notes tips for setting direct I/O on Sun
Solaris Oracle servers using filesystemio_options=setall and
forcedirectio. Also see the
AIX - High-DML
database may want to use direct I/O (DIO instead of JFS or CIO.
See these notes on using
Oracle AIX with direct I/O which is
implemented with the AIX "dio" option
and the init.ora parameter filesystemio_options:
Oracle init.ora: filesystemio_options
JFS2 mount option: mount
–o dio /oradata/ts1.dbf
Veritas VxFS - (including HP-UX, Solaris and AIX),
look for "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 man page for hints.
For HPUX, see
Oracle on HP-UX – Best Practices.
Linux - Linux systems support direct I/O on a per-filehandle
basis (which is much more flexible).
Also with 10g and beyond, this feature is already working which
means that is does not require any patch. For Oracle9i,
the DBA will need to download <2448994> - Abstract: DIRECT
IO SUPPORT OVER NFS2448994>. To enable direct I/O support:,
check these settings:
- 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.
Kernel Asynchronous I/O (AIO) Support for Linux and
this great OTN article:
Talking Linux: OCFS Update.
If you are Oracle9i release2, you
can track I/O for specific Oracle tables and indexes. This
allows you to see the specific sources of physical I/O.
On Oracle10g, AWR also provides the
dba_hist_filestatxs table to track disk I/O:
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
Here is some sample output:
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
24-FEB-04 188.8.131.526 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
To check details on file I/O in
Oracle, I use our script collection.
You can download them immediately at this link:
I had a book shelf of Oracle tuning books in my office; half of the
them were authored by Mr. Burleson. These books have given me
tremendous help in tuning our systems.
For example, from Burleson's "Oracle Tuning - The Definitive
Reference", I learned that using direct I/O for data files will
bypass caching at the OS
layer and yield optimal disk performance. This is a feature
that I'd always wanted to implement but hesitated to do so in our
production system, since it must be enabled at both Oracle and the
I don't want to put it into production unless I am absolutely
convinced that it would yield better performance. Last July, I had
the opportunity to test direct I/O during our hardware upgrade from
IBM S85 to P570.
After installing OS and Oracle software on the new system, I
migrated the database by exporting production databases from the old
server and then importing to the new server. I thought import is the
most brutal test. The import completed in 7 hour 23 minutes
without direct I/O and completed in 5 hour and 45 minutes with
direct I/O enabled. Now, direct I/O is enabled for all of our
production and development systems.
Sharon Xiaofang Meng