This is a common practice to bump the size of all buffer pools in DB2 as that gives the maximum performance boost. It is true but as a DBA you will like to monitor the buffer pool hit ratio and use of the buffer pool to make sure that you are not wasting your memory.[Note: If you are using STMM to manage the size of buffer pools automatically, you still want to read this article as it is important.]
Getting buffer pool hit ratio is very simple. You just use this SQL.
SELECT BP_NAME, TOTAL_HIT_RATIO_PERCENT FROM SYSIBMADM.BP_HITRATIO;
But, above SQL only tells the ratio between logical and physical reads against the buffer pool. How do you determine if there are free unused pages but allocated.
The best indicator to see if you have unused but allocated pages is to see at number of physical reads. If number of physical reads is less than total number of pages for that buffer pool, you have unused pages in your buffer pool. For example, my IBMDEFAULTBP buffer pool size is 250 pages and total number of data physical read is 261. This means that there are no unused pages in my buffer pool.
C:\>db2 get snapshot for bufferpools on sample Bufferpool Snapshot Bufferpool name = IBMDEFAULTBP Database name = SAMPLE Database path = C:\DB2\NODE0000\SQL00002\ Input database alias = SAMPLE Snapshot timestamp = 02/28/2008 14:44:56.264382 Buffer pool data logical reads = 8599 Buffer pool data physical reads = 261 Buffer pool temporary data logical reads = 71 Buffer pool temporary data physical reads = 0 Buffer pool data writes = 0 Buffer pool index logical reads = 3785 Buffer pool index physical reads = 266
But, the main discussion point here is the size of the temporary buffer pool. I have seen that DBAs allocate huge number of pages for temporary buffer pool thinking that they will get performance benefit but it is not always true.
As a best practice, you should keep the size of your temporary buffer pool small if you have DB2 Stored Procedures creating and dropping temporary tables and these stored procedures are used very heavily. In this case, you are best left with a small buffer pool for that temporary table space.
Why is this so? Because, DB2 maintains a list of dirty pages of buffer pool and this is a list of pages that have been modified in memory and DB2 should flush these pages at some time back to disk. But if you drop temporary table, DB2 has to scan this list for pages affected by the dropped table. If your buffer pool is too large and your list of dirty pages will also be large. This will result in lots of CPU usage on your system and latch contentions. You will be much better off with having a small buffer pool size used for temporary table space if you have a heavy use of a stored procedure that does drop and creation of temporary table.