Managing multi-block Oracle reads

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:

http://www.dba-oracle.com/oracle_tips_direct_io.htm 

For complete details, see my master book "Oracle Tuning: The Definitive Reference":

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

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:

- AIX - Look for a "dio" option.
 

- 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).

Next, you need to set db_file_multiblock_read_count:

http://www.dba-oracle.com/t_db_file_multiblock_read_count.htm 
 

*****************************************

Is your Database Healthy?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

BC has a great Oracle health check where we identify all database bottlenecks to ensure that your misison-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call me at 800-766-1884 for details, and check-out our on-site Oracle training catalog:

http://www.dba-oracle.com/bc-catalog.pdf