What is bufferpool hit ratio?

What is bufferpool hit ratio?

Buffer pool hit ratio is a measure of how often a page access (a getpage) is satisfied without requiring an I/O operation. You can help some of your applications and queries by making the buffer pools large enough to increase the buffer hit ratio.

How to increase bufferpool hit ratio in DB2?

The following 3 ways should help:

1. reorg objects to make object size smaller and page utilization higher.
2. tune SQL access plan to reduce the number of unnecessary pages being read.
3. separate the buffer pool of temporary tablespace and permanent tablespace.

How does bufferpool work in DB2?

The ALTER BUFFERPOOL statement can be used to increase the size of a buffer pool. By default, and if sufficient memory is available, the buffer pool is re-sized as soon as the statement executes. If sufficient memory is unavailable when the statement executes, memory is allocated when the database reactivates.

How is buffer hit ratio calculated?

To calculate a hit ratio, divide the number of cache hits with the sum of the number of cache hits, and the number of cache misses. For example, if you have 51 cache hits and three misses over a period of time, then that would mean you would divide 51 by 54. The result would be a hit ratio of 0.944.

What should my buffer pool hit ratio be?

Buffer pool hit ratio (ratio of GETPAGEs resolved without read I/Os / total GETPAGEs) – This value measures overall buffer pool efficiency. Your goal is to have as high a number as possible. Generally, a lower value indicates a need for a larger buffer pool.

How do you know if your DB2 buffer pools are working?

( BMC System Performance for DB2 provides both types of tools.) DB2 performance monitors monitor buffer pools in real time and may provide near and long-term historical reporting capabilities. Monitors report DB2 accounting and statistics data about buffer pool performance.

What should my hit ratio be in DB2?

If you have high hit ratio (ideally, over 99%), then you are doing a good job of avoiding I/Os. How to get to that 99%+ is the question. The “easiest” way is to keep hrowing memory at the pools until you reach your target hit ratio.

Do you need to know the BP hit ratio?

Gopinath. The very short answer, in my opinion, is yes . . . at a very high level, BP hit ratio will give you a decent view of performance of the BP’s. The entire purpose of the BP’s is to try to avoid I/O’s.