Can Read ahead caches is able to eliminate disk io of OLTP applications?

Many disk-management software and hardware vendors provide a buffer cache. This cache can be anywhere from 512 MB to 4GB. Most of the time, these caches have a specific purpose in life. Vendors call them write or write-back caches, read-ahead caches, or read/write caches. Read-ahead caches may be provided independent of any RAID configuration and are similar in nature to the Unix file buffers. In read-intensive DSS applications, they perform a valuable role: pre-reading. During sequential reads (such as full table scans and full index scans), they pre-read the data and store it in the buffer cache. This causes subsequent requests for data to be satisfied via a logical read from memory and enhances performance by reducing the number of total physical reads. However, they are not desirable for OLTP applications, where the reads are small and random in nature. Most pre-reading amounts to a waste of CPU time. In such applications, a little sequential read (an index scan, for example) would induce cache read-aheads.

Since the reads are mostly random, the next read would read another disk or another cylinder of the same disk, thereby wasting the pre-read data in the cache. Accordingly, it is better not to rely on such read-ahead caches for OLTP applications, but to disable the read-ahead algorithm, whenever possible.

If your database uses Unix file systems, reading multiple OS blocks can also trigger a read-ahead, where the file system buffers are populated with pre-read data. This is especially important if your DB_BLOCK_SIZE is a multiple of the OS block size. For example, if your OS block size is 512 bytes and your DB_BLOCK_SIZE is 8 KB, then each database block retrieved will always cause read-aheads, since one database block corresponds to multiple OS blocks. Even a simple SELECT based on a unique index would cause at least one I/O to the table (unless the index itself satisfies the query) and two or more I/O to the index (depending on the index depth). In this case, the index root node fetch may cause (say, on a sequent) a 64 KB I/O for the index leaf node, followed by another 64 KB I/O for the table block. This can be a genuine irritant for OLTP databases, since in addition to causing extra I/O, it is also cleaning out the Unix buffer cache of "hot" data. The easiest way to limit such occurrences is to ensure that the OS block size matches the database block size.

Hence, we can say that enabling read-ahead cache is useful to improve performance of DSS systems but it not useful for OLTP databases. Sometimes it can be adverse effect of performance of OLTP database while using read-ahead caches. Enabling read-ahead cache configuration is not recommended for random data fetching SQL environment.

Full database health checkup is available free of cost. High experienced remote dba experts are available to analysis your critical Oracle databases and providing full report of Oracle database with potential advices and guidance for improving security of database and tuning of Oracle database server and applications. Dbametrix offers solid and consistent remote dba support with low cost and customized remote plans. For more details kindly contact our expert team.