VERITAS Storage Foundation 4.1 Oracle Administrator's Guide

Chapter 4, Using VERITAS Cached Quick I/O
Prerelease 8 September 2005, 8:54am Determining Candidates for Cached Quick I/O
117
Analyzing I/O Statistics
The output of the qiostat command is the primary source of information to use in deciding
whether to enable or disable Cached Quick I/O on specific files. Statistics are printed in two lines
per object. The second line of information is defined as follows:
◆ CREAD is the number of reads from the VxFS cache (or total number of reads to Quick I/O
files with cache advisory on)
◆ PREAD is the number of reads going to the disk for Quick I/O files with the cache advisory on
◆ HIT RATIO is displayed as a percentage and is the number of CREADS minus the number of
PREADS times 100 divided by the total number of CREADS. The formula looks like this:
(CREADs - PREADs) * 100/ CREADs
The qiostat -l command output looks similar to the following:
OPERATIONS FILE BLOCKS AVG TIME(ms)
CACHE STATISTICS
FILE NAME READ WRITE READ WRITE READ WRITE
CREAD PREAD HIT RATIO
/db01/cust.dbf 17128 9634 68509 38536 24.8 0.4
17124 15728 8.2
/db01/system.dbf 6 1 21 4 10.0 0.0
6 6 0.0
/db01/stk.dbf 62552 38498 250213 153992 21.9 0.4
62567 49060 21.6
Analyze the output to find out where the cache-hit ratio is above a given threshold. A cache-hit
ratio above 20 percent on a file for a given application may be sufficient to justify caching on that
file. For systems with larger loads, the acceptable ratio may be 30 percent or above. Cache-hit-ratio
thresholds vary according to the database type and load.
Using the sample output above as an example, the file /db01/system.dbf does not benefit
from the caching because the cache-hit ratio is zero. In addition, the file receives very little I/O
during the sampling duration.
However, the file /db01/stk.dbf has a cache-hit ratio of 21.6 percent. If you have determined
that, for your system and load, this figure is above the acceptable threshold, it means the database
can benefit from caching. Also, study the numbers reported for the read and write operations. When
you compare the number of reads and writes for the /db01/stk.dbf file, you see that the
number of reads is roughly twice the number of writes. You can achieve the greatest performance
gains with Cached Quick I/O when using it for files that have higher read than write activity.