Oracle fast disk Direct I/O tips

awirpt.sql:

http:///SP/ 

root> $ORACLE_HOME/rdbms/admin/awrrpt.sql

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
                                                         % Total
Event                         Waits        Time (s)     Ela Time
--------------------------- ------------ ----------- -----------
db file sequential read       2,598        7,146           48.54
db file scattered read       25,519        3,246           22.04
library cache load lock         673        1,363            9.26
CPU time                      2,154          934            7.83
log file parallel write      19,157          837            5.68
 

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./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./art_dbazine__multiblock.htm

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

http://www./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).  Also with 10g and beyond, this feature is already working which means that is does not require any patch.  For Oracle, the DBA will need to download - Abstract: DIRECT IO SUPPORT OVER NFS.  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.

  See Kernel Asynchronous I/O (AIO) Support for Linux  and this great OTN article: Talking Linux: OCFS Update.

If you are Oraclerelease2, 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./oracle_tips_r2_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:

BEGIN_INTERVAL_TIME       FILENAME                                 PHYRDS
------------------------- ----------------------------------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    164,700
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,082
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF    472,008
                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA      2,123


24-FEB-04 11.30.18.296 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF    167,809
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF    26,248
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF    476,616
                                                                                                                     

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

http://www./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