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
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
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:
Oracle supports multiple blocksize tablespaces in a
single database including 2k, 4k, 8k, 16k and 32k
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
goes on to note that direct I/O can increase the size of
the RAM available for the working set in the Oracle data
"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
This can be done by allowing disk_asynch_io to default to
TRUE, but setting filesystemio_options to either none or
Note: Oracle now says that the preferred method is
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
Glen Faucett also notes tips for setting direct I/O
on Sun Solaris Oracle servers using filesystemio_options=setall
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
SETALL: enable both
asynchronous and direct I/O on file system
NONE: disable both
asynchronous and direct I/O on file system
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
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:
begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
Here is some sample output
showing physical I/O at the data file level:
24-FEB-04 11.00.32.000 PM
24-FEB-04 184.108.40.2066 PM
To check details on file I/O in Oracle, I use my
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).
sys@xxxxxxxxx> alter system flush
[ first flush the buffer cache ]
[ 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
O_RDWR|O_DSYNC) = 11
24399: -> libc:directio(0x10f, 0x0, 0x1, 0x0)
24399: ioctl(271, 0x2000664C, 0x00000000)
The 3rd parameter to the ioctl() call is 0 for directio_off, and 1