Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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.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__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).  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.dba-oracle.com/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 collectionYou 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
 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.