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:

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 performance.

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:

http://www.dba-oracle.com/art_builder_statspack_io.htm

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 from disk:

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 performance:

http://www.dba-oracle.com/art_dbazine_9i_multiblock.htm

For information about Oracle direct I/O, refer to this URL by Steve Adams:

http://www.ixora.com.au/notes/filesystemio_options.htm

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 direct I/O:

• 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 Sun documentation.  Also, Glen Faucett also notes tips for setting direct I/O on Sun Solaris Oracle servers using filesystemio_options=setall and forcedirectio.  Also see the 10g patch required for DirectIO on Solaris.
   

• 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 = SETALL
  or
  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), and I believe Oracle enables this feature automatically.  Someone should verify at what release Oracle started to support this feature (it is called O_DIRECT). See 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. 

http://www.dba-oracle.com/oracle_tips_9ir2_statspack.htm

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

select
   begin_interval_time,
   filename,
   phyrds
from
   dba_hist_filestatxs
natural join
   dba_hist_snapshot;

Here is some sample output:

To check details on file I/O in Oracle, I use our script collection.  You can download them immediately at this link:

http://www.rampant-books.com/download_adv_mon_tuning.htm

---

Reader feedback:

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 OS level.

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
Oracle DBA
Ampacet Corp