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
initialization parameter. If you're
going to adjust the size, then you should follow this process:
- Note the current cache hit ratio.
- Increase the
- 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:
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.