DB2 FOR LUW / DB2FORLUW / B50 - DB2

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).

****************************************************************
* USERS AFFECTED:                                              *
* all                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to 11.5 FP7 or later                                 *
****************************************************************

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('

****************************************************************
* USERS AFFECTED:                                              *
* all                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to 11.5 FP7 or later                                 *
****************************************************************

Upgrade to 11.5 FP7 or later

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

23.06.2021
21.11.2021
21.11.2021