Author: Don Burleson

Read-ahead caching started on IBM mainframes as sequential prefetch. The Read-ahead caching has many names on different operating systems. In this article I will discuss if Read-ahead cache is right for your database or not.

Oracle Read-ahead cache:

The concept of Read-ahead caching is simple. Over 90% of I/O latency is consumed in the read-write head movement, as the heads are placed under the target cylinder.  Once in-place, the disk continues to spin and the read-write head can simultaneously transmit back the original block request at the same time as the next sequential block passes below the read-write heads.

Read-ahead cache for Scan Operations:

A read-ahead cache can be very useful for speeding up the scan operations like index range scans, index fast full scans, and full-table scans.

Read-ahead cache for adjacent data blocks:

The read-ahead cache may improve I/O throughput if your database is requesting adjacent data blocks.

Read-ahead cache for non-adjacent data blocks:

In RAID10 SAME, stripe and mirror everywhere, like with ASM, the blocks are not adjacent, and a read-ahead cache may not be as useful as a database where the data blocks are laid-out sequentially.  The stripe width influences this decision.

Minimizing read-write head latency:

Placing adjacent blocks together on disk when you know that they will be accessed sequentially is analogous to the process of placing high-impact files near the middle absolute track of huge Oracle disks, to minimize read-write head latency.

Read-ahead caching for Oracle database:

Enabling read-ahead cache will greatly improve the I/O throughput for scan operations. 

In a normal database you should look for "db file scattered reads", and see if the latency is less than "db file sequential reads", which happen on single block fetch operations.

Below table shows "Read-ahead cache" on Windows 2003 Sp2 SAN configuration. The nature of the application is high volume very random selects and huge amount of inserts. Below are top-5 events from an AWR report:

Top 5 Timed Events
                                                     Pct

The Read-ahead cache is only useful in cases of "db file scattered reads" i.e. full table scans. In the above example AWR report there is 2.3% of I/O as scattered reads and therefore a read-ahead may not help much.

Finding disk I/O latency:

The WISE tool is the easiest way to analyze STATSPACK and AWR disk I/O data in Oracle. 

WISE tool allows you to spot hidden I/O trends and times when full-scan operations are important.

Cost of enabling read-ahead cache:

Most Oracle database process a wide range of transactions and an OLTP database which does few "db file scattered reads" may perform lots of full-scans during batch reporting windows.

You need to compare the costs of enabling your read-ahead cache with the benefits in speeding-up "db file scattered reads".  This can easily be confirmed in a STATSPACK or AWR report.

Conclusion:

To conclude I would say that a transaction that does not require adjacent data block will not benefit from a read-ahead cache, while transactions that perform index range scans or full-table scans will benefit the most from a read-ahead cache.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Efficient Tips for Renaming Oracle Instance!!
   Oracle10g Wait Event Tuning
   A Guide to Oracle Bitmap Index Techniques!!
   Releasing Oracle Unused Space: Quick & Efficient Solution!!