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

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Using direct I/O with Oracle

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 asynchronous I/O. 

First, note the differences between Oracle server Internal file cache vs. JFS cache

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.

Example Statspack output:

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

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:

For optimal disk performance, Oracle should always use direct I/O to its data files, bypassing any caching at the OS layer. This must be enabled both in Oracle and in the operating system. For information about Oracle direct I/O, refer to this URL by Steve Adams:


The debate:

Oracle supports multiple blocksize tablespaces in a single database including 2k, 4k, 8k, 16k and 32k blocksizes.

The debate is about the benefit of different blocksizes (e.g. 8k, 32k) for reducing Oracle physical disk I/O and about whether the SA is likely to configure the Oracle server to use direct I/O for Oracle.

Some claim that placing large-related things (i.e. indexes) in a large blocksize results in a single physical fetch (less disk I/O), but others say this is NOT true because the OS blocksize (and the JFS cache) result in multiple OS I/O.

According to this web page on direct I/O by Steve Adams:

"It also avoids performance problems associated with using database block sizes that do not match the file system buffer size exactly. (more).

Whether direct I/O is available, and the way in which it can be enabled, are dependent on the operating system and file system type in use. Direct I/O can sometimes be enabled for an entire file system, using a file system mount option. Application programs may also be able to request direct I/O for a particular file by setting a flag when opening the file."

Adams goes on to note that direct I/O can increase the size of the RAM available for the working set in the Oracle data buffers:

"By switching to raw or direct I/O and by giving Oracle the memory that would otherwise be used by the operating system to cache Oracle data, a much larger working set of data can be cached, and a much higher cache hit rate can be sustained with obvious performance benefits."

Oracle controls direct I/O with a parameter named filesystemio_options. According to this page by Steve Adams the filesystemio_options parameter must be set in order for Oracle to read data blocks directly from disk

"Databases that use a combination of say raw log files and raw tempfiles with file system based datafiles may wish to use kernelized asynchronous I/O against the raw files, but to avoid inefficient threaded asynchronous I/O against the datafiles.

This can be done by allowing disk_asynch_io to default to TRUE, but setting filesystemio_options to either none or directIO."

Note:  Oracle now says that the preferred method is filesystemio_options=setall.

Checking your Server

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 of filesystemio_options=directio:

FILESYTEMIO_OPTIONS can be set to one of the following values:

  • ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.

  • DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.

  • SETALL: enable both asynchronous and direct I/O on file system files.

  • NONE: disable both asynchronous and direct I/O on file system files.

The first step to avoiding buffered IO is to use the "FILESYSTEMIO_OPTIONS" parameter. When you use the "SETALL" option, this sets all the options for a particular filesystem to enable directio or async IO. Setting the FILSYSTEMIO_OPTIONS to anything other than "SETALL" could reduce performance.

With UFS, the only way to bypass the page cache is with directio. If you are using Oracle 9i or greater, then set the FILESYSTEMIO_OPTIONS=SETALL init.ora parameter. This the preferred way of enabling directio with Oracle. With this method, Oracle uses an api to enable directio when it opens database files. This method allows you to still use buffered IO for operations like backup and archiving. If you are using Oracle 8i, then the only way to enable directio with UFS is via the forcedirectio mount option.

  • AIX - Look for a "dio" option.  Here is a great link for AIX direct I/O: 
  • 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. 

On Oracle10g, AWR also provides the dba_hist_filestatxs table to track disk I/O.  Here is a script:

break on begin_interval_time skip 2

column phyrds format 999,999,999
column begin_interval_time format a25

natural join

Here is some sample output showing physical I/O at the data file level:

BEGIN_INTERVAL_TIME       FILENAME                                 PHYRDS
------------------------- ---------------------------------------- ------------
24-FEB-04 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 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 my Oracle script collection

The Ion tool is the easiest way to analyze disk I/O in Oracle and Ion allows you to spot hidden I/O trends.


Verifying direct I/O

Each OS has different commands to verify that your instance is using direct I/O.  For Sun, this note explains how to verify that you are using direct I/O with Oracle Solaris 9 (sparc 64-bit).

 [ first flush the buffer cache ]

sys@xxxxxxxxx> alter system flush buffer_cache;
System altered.

[ Then issue (as user oracle) ]

oracle@ironman:~$ truss -f -t open,ioctl -u ':directio' sqlplus user/pass
... (lots of output here)

[ Then type ]

create table a as select * from big_table;

You will see that for every data file opened, directio is explicitly turned off:
24399:  open("/u04/oradata/BIA/APM_DATA13.dbf", O_RDWR|O_DSYNC) = 11
24399:  -> libc:directio(0x10f, 0x0, 0x1, 0x0)
24399:  ioctl(271, 0x2000664C, 0x00000000)              = 0

The 3rd parameter to the ioctl() call is 0 for directio_off, and 1 for directio_on.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational