Lesson 11	Improving the buffer cache hit ratio
Objective	Improve the cache hit ratio.

Improving the buffer cache hit ratio

If your buffer cache hit ratio is poor, you can usually improve it by increasing the size of the buffer cache. The size of the buffer cache is controlled by the db_block_buffers initialization parameter. If you're going to adjust the size, then you should follow this process:

Note the current cache hit ratio.
Increase the db_block_buffers parameter.
Stop and restart the database.
Note the new cache hit ratio.
If a significant increase has occurred, go back to step 2 and increase the buffer cache some more.
If a significant change has not occurred, back out the current change and run with the previous buffer cache size.

At some point, your hit ratio will level out, and increasing the buffer cache further won't affect the hit ratio. The idea is to detect that leveling-out point and size the buffer cache just large enough to achieve it. The following mouseover illustrates this more clearly:

A buffer cache size of 5,000 results in a hit ratio of 0.60.

Increasing the cache size to 6,000 buffers yields a large increase in the hit ratio, which is now 0.80.

Going up to 7,000 buffers yields a smaller increase, and the hit ratio is now 0.90.

Further increasing the cache to 8,000 buffers yields an even smaller increase, taking the hit ratio to 0.95.

Increasing the cache from 8,000 to 9,000 buffers results in no increase to the hit ratio. You should undo this change and run with an 8,000 buffer cache.

hit Ratio Histogram

You may be tempted to increase the buffer cache beyond the point at which the hit ratio levels off. Don't do that--there's absolutely no benefit. you will just be wasting memory that you may be able to use profitably elsewhere.
In the next lesson, you will compute the buffer cache hit ratio for a specific period of time.