|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.
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');
db block gets 7615
consistent gets 551784
physical reads 4817
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)
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.
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.