How to Make Things Faster

Chapter 83. The Hit Rate Problem

Several years ago, the Oracle world—followed by the MySQL world and some other worlds as well—underwent a cultural shift in how people measured performance. It was a shift away from judging the performance of a system based primarily upon a metric called the cache hit ratio.

The Oracle Database product uses a shared memory structure called the database buffer cache. It’s where Oracle sessions manage database blocks that they’ve read recently from the operating system. Finding a block in the buffer cache (a cache hit) is quicker and less expensive than not finding it (a cache miss). When the block that an Oracle session needs is not in the buffer cache, it takes extra instructions, including an operating system read call, to put it there.

The cache hit ratio (CHR) is the ratio of hits to attempts. Since Oracle directly measures misses instead of hits, people calculate hits as attempts minus misses (a − m), yielding the formula CHR = (a − m)/a. For example:

Attempts	Misses	Hits	Hit ratio
(a)	(m)	(a − m)	((a − m)/a)
100	0	100	100.0%
100	30	70	70.0%
100	100	0	0.0%
10,392	572	9,820	94.5%

A ratio defined this way seems like a perfectly obvious higher-is-better metric. For example, imagine that SQL statements A and B both return identical results. Which statement would you want to use?

Measure	SQL A	SQL B
Attempts	100	100
Misses	10	1
Hits	90	99
Hit rate	90%	99%

The right answer is SQL B, because its execution has fewer ...