Calculating the Cache Hit and Miss Rates

The strategy of increasing the size of the database buffer cache ( db_block_buffers) to improve the cache hit rate is fairly simple, and is perhaps the best understood aspect of Oracle tuning. However, as always with Oracle, significant complexity lurks below the surface.

How do you calculate the cache hit rate?

The database buffer cache hit rate is normally calculated from the following three statistics as reported in V$SYSSTAT.

consistent gets

The number of blocks requested in consistent mode. Blocks are requested in consistent mode for consistent read operations. When a block is requested in consistent mode, if any changes have been committed or made to that block since the requesting statement (or transaction) began, then they must be rolled back for the purposes of the read, to give a consistent view of the data at that time.
Note, however, that due to a long-standing Oracle bug the consistent gets statistics actually overstates the number of consistent gets very significantly. For this reason, it is better to use the sum of no work - consistent read gets, cleanouts only - consistent read gets, rollbacks only - consistent read gets and cleanouts and rollbacks - consistent read gets at this point.

db block gets

The number of blocks requested in current mode. Blocks must be requested in current mode in order to be changed. Also certain classes of blocks, like segment header blocks are always requested in current mode.

physical reads

The number of database blocks which the database instance has requested the operating system to read from disk. This is typically greater than the actual number of disk read operations performed, because of multiblock reads and because some read requests may be satisfied from the operating system buffer cache.

Each consistent get or db block get represents a logical read. Each logical read for which there was no corresponding physical read, represents a cache hit. That is, the requested block was already present in the database buffer cache, and so no physical read request was issued. The cache hit rate is the ratio of cache hits to logical block reads, normally expressed as a percentage.

What are direct reads?

Now for a twist. You have probably assumed that if your cache hit rate is 85%, then your cache miss rate is 15%. Not so. Oracle actually performs direct reads for certain operations. So it is possible to have an 85% cache hit rate and a 1% cache miss rate, with the remaining 14% being accounted for by direct reads.

Direct reads are performed for parallel scans, and reads from temporary tablespaces. Blocks are read directly into private buffers in the PGA, rather than into the database buffer cache in the SGA. There are no cache hits, because blocks are not searched for in the cache before being read. And there are no subsequent cache hits, because the blocks are just discarded after use, rather than cached. However, this is no great loss. The possibility of getting enough cache hits in equivalent cached operations to actually save disk reads is almost negligible, while the possibility of losing cache hits because of the additional load on the cache is quite significant. So, direct reads actually improve the cache hit rate. They also improve block access concurrency by removing a significant load from the buffer cache latches.

Incidentally, direct reads can also be obtained for serial scans using the _serial_direct_read parameter in 8.1, or event 10355 in earlier releases.

How do you calculate the cache miss rate?

This discussion of direct reads leads naturally to the observation that it is really the cache miss rate, rather than the cache hit rate, that should influence your sizing of the database buffer cache. A cache hit rate of only 85% may well lead you to enlarge the buffer cache. Whereas, a miss rate of just 1% may not.

Oracle 8

In release 8.1, it is easy to calculate the cache miss rate exclusive of direct reads. It is essentially just a matter of subtracting the value of the statistic physical reads direct from the number of physical reads. However, an even better approach, that works for release 8.0 also, is to calculate separate miss rates for each of the three buffer pools using the statistics reported in V$BUFFER_POOL_STATISTICS, instead of a single miss rate for the database buffer cache as a whole. Prior to release 8.1.6, the V$BUFFER_POOL_STATISTICS view must be created by running the $ORACLE_HOME/rdbms/admin/catperf.sql script. Alternatively, the APT script buffer_pool_miss_rates.sql can be used to get this information directly from the underlying X$ tables. The physical reads reported against each buffer pool are exclusive of direct reads. They are not, however, exclusive of the multiblock reads associated with serial full table and index scans.

Oracle 7.3

Unfortunately, Oracle7 does not provide statistics to enable you to distinguish between direct reads and cache misses, so the cache miss rate cannot be calculated. However, some careful thought does yield an alternative and indeed more useful statistic.

Because cache hits are rare for serial long table scans, and impossible for direct reads, it is reasonable to assume that the cache miss rate can only be reduced by avoiding single block reads. That is, that cache misses associated with multiblock reads are not tunable. Therefore, the tunable cache miss rate can be estimated as the number of single block reads (taken from V$SYSTEM_EVENT) over the number of logical reads minus the number of blocks read for multiblock reads. The APT script tunable_cache_miss_rate.sql calculates this statistic.