 |
|
Tuning multi-block I/O in Oracle
Oracle Tips by Burleson Consulting |
Multi-block reads (index range scans, full-table scans, index
fast-full scans) are very common in Oracle, and the DBA must
understand how to tune their disk I/O subsystem for multi-block
reads.
The first step is ensuring that your Oracle database is properly
configured for direct I/O.
10gr2 Note: Starting in
Oracle 10g release2, Oracle recommends not setting the
db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
Direct I/O and Async I/O 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.
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" option.
Oracle DBAs claim this option makes a huge difference in I/O speed
for Sun servers.
Glen Faucett also notes tips for setting direct I/O on Sun Solaris Oracle
servers using filesystemio_options=setall and forcedirectio: Here is the Sun documentation:
http://docs.sun.com/db/doc/816-0211/6m6nc6713?a=view
·
AIX - Look for a "dio" option. Here is a great
link for AIX direct I/O:
http://www-106.ibm.com/developerworks/eserver/articles/DirectIO.html
·
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.
Next, you need to set db_file_multiblock_read_count:
http://www.dba-oracle.com/t_db_file_multiblock_read_count.htm
Remember, the parameter db_file_multiblock_read_count is only
applicable for tables/indexes that are full scanned, but it also
effects the SQL optimizer in its calculation of the cost of a
full-table scan.
According to Oracle, this is the formula for setting
db_file_multiblock_read_count:
max I/O chunk size
-------------------------
=
db_file_multiblock_read_count
db_block_size
But how do we know the value of the max I/O chunk size?
The maximum effective setting for db_file_multiblock_read_count
is OS and disk dependant. Steve Adams, an independent Oracle
performance consultant (see
www.ixora.com.au ), has published a helpful script to assist you
in setting an appropriate level. This script conducts a test and
sample actual I/O chunk sizes on your server to aid you in setting
db_file_multiblock_read_count:
http://www.ixora.com.au/scripts/sql/multiblock_read_test.sql
