Lesson 10	The database buffer cache hit ratio
Objective	Compute the cache hit ratio.

Database Buffer Cache hit ratio

Recall that the database buffer cache is an area in memory where Oracle caches recently used database blocks so that they don't have to be reread from disk if they are needed again. The buffer cache allows you to take advantage of the fact that most database activity tends to affect a relatively small percentage of blocks. The more often a block is found in the buffer cache as opposed to being read from disk, the better your database's performance will be. The buffer cache hit ratio is a measure of how often that happens.

Statistics needed

To compute the buffer cache hit ratio, you need to know how often Oracle needed a block, and you need to know how often blocks had to be read from disk. You can get that information by querying the V$SYSSTAT view, as shown here:

SQL> SELECT name, value

2  FROM v$sysstat

3  WHERE name IN 

4    ('physical reads', 'db block gets','consistent gets');

NAME                        VALUE

----------------------- ---------

db block gets                7615

consistent gets            551784

physical reads               4817

The physical reads statistic tells you the number of blocks Oracle had to read from disk physically. The other two statistics combine to tell you the total number of blocks that Oracle accessed while executing SQL statements. Obviously, some blocks get accessed more than once.

Computing the buffer cache hit ratio

Once you have the correct statistics, plug them into the following formula to compute the hit ratio:

1 - (physical reads / (db block gets + consistent gets))

Using the statistics shown above, the hit ratio would be computed as follows:

1 - (4817 / (7615 + 551784))

= 1 - (4817 / 559399)

= 1 - (0.0086)

= .9914

The buffer cache hit ratio in this case is 0.99, which is high. Some DBAs choose to express this as a percentage, and show it as 99%. Oracle recommends that you maintain a hit ratio of 0.90 or higher. Some DBAs prefer to shoot for a higher target, such as 0.95.

Caveats

Be careful not to calculate the hit ratio too soon after starting a database. It takes time for the buffer cache to fill, and until that happens, your hit ratio will appear to be very poor. Compute it only after the database has been running for a while.
In the next lesson, you will learn how to improve the cache hit ratio.