DB2 - Problem description
Problem IT37394 | Status: Closed |
DB2PD -BUFFERPOOLS REPORTS INACCURATE HIT RATIO | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
db2pd -bufferpools will report inaccurate bufferpool synchronous read hit ratios when there is a significant amount of prefetching. The inaccuracy is due to DatLRds counting only synchronous (agent-only) reads, whereas DatPRds is counting synchronous + asynchronous (agent + prefetcher) reads. Consider the following db2pd -bufferpools output: Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic 0x00007F3A5CFDE020 1 IBMDEFAULTBP 4096 484304 0 0 5 0 484304 484304 0 True BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio 1 530 498 06.04% 0 0 00.00% 5 1 80.00% 0 0 00.00% BPID DataWrts IdxWrts DirRds DirRdReqs DirRdTime DirWrts DirWrtReqs DirWrtTime 1 0 0 52 1 0 0 0 0 BPID AsDatRds AsDatRdReq AsIdxRds AsIdxRdReq AsRdTime AsDatWrts AsIdxWrts AsWrtTime 1 490 16 0 0 43 0 0 0 BPID TotRdTime TotWrtTime VectIORds VectIOReq BlockIORds BlockIOReq FilesClose NoVictAvl UnRdPFetch 1 75 0 490 16 0 0 0 0 0 In this output the hit ratio is reported as 6.04% when using the formula (100*(DatLRds-DatPRds)/DatLRds). However, AsDatRds indicates that 490 out of the 498 physical reads were done asynchronously by prefetching. Therefore, agents only experienced 8 cache misses rather than the 498 cache misses used in the hit ratio calculation. The real synchronous read hit ratio is therefore 98.49% when using the formula (100*(DatLRds-(DatPRds-AsDatRds)/DatLRds). | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.1.4.7 * **************************************************************** | |
Local Fix: | |
Do not use db2pd -bufferpools to determine a bufferpools hit ratio. Instead reference the *_HIT_RATIO_PERCENT fields in the SYSIBMADM.MON_BP_UTILIZATION administrative view. If the a tablespace?s hit ratio is desired, fields in the MON_GET_TABLESPACE table function can be used to calculate different types of hit ratios: SELECT -- Agents synchronous read hit ratio 100 * ( POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS - ( POOL_DATA_P_READS + POOL_TEMP_DATA_P_READS - POOL_ASYNC_DATA_READS )) / NULLIF (POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS, 0) AS SYNC_HR -- Prefetchers asynchronous read hit ratio , 100 * (POOL_ASYNC_DATA_LBP_PAGES_FOUND) / NULLIF (POOL_ASYNC_DATA_LBP_PAGES_FOUND + POOL_ASYNC_DATA_READS, 0) AS ASYNC_HR -- Combined synchronous and asynchronous hit ratio , 100 * (POOL_DATA_LBP_PAGES_FOUND) / NULLIF (POOL_DATA_LBP_PAGES_FOUND + POOL_DATA_P_READS + POOL_TEMP_DATA_P_READS + POOL_ASYNC_DATA_READS, 0) AS OVERALL_HR FROM TABLE(MON_GET_TABLESPACE(' | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.1.4.7 * **************************************************************** | |
Comment | |
Upgrade to 11.1.4.7 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 23.06.2021 17.04.2022 17.04.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |