Hit ratios and their role in performance improvement

High hit ratios indicate that the database is memory-efficient. For example, a high hit ratio for buffer cache says that, most of the time, the database will find the database records it needs in the buffer pools, instead of reading it from disk. Having fewer physical reads leads to better performance.

Getting ready

We have discussed, in Chapter 12, Monitoring, setting up monitor switches. Make sure monitor switches are on for buffer pools. Follow indicators regularly and tune caches in accordance. You may want to run stats on all the tables and probably check for fragmentation.

How to do it...

1. Monitor buffer pool hit ratios . Hit ratios with 90 percent or more indicate a good hit ratio:

   [db2inst1@nodedb21 ~]$ db2 'SELECT DBPARTITIONNUM "PARTN",
   >      SUBSTR(BP_NAME,1,20) "BUFFERPOOL",
   >      (POOL_DATA_L_READS - POOL_DATA_P_READS) * 100 /
   >      (POOL_DATA_L_READS + 1) "HIT RATIO"
   >      FROM   SYSIBMADM.SNAPBP'
   
   PARTN  BUFFERPOOL...