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.

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:

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

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

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

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

To check details on file I/O in Oracle, I use my Oracle script collection.