IBM Support

IT37394: DB2PD -BUFFERPOOLS REPORTS INACCURATE HIT RATIO

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

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

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('<tablespace_name', -2));
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.7                                          *
    ****************************************************************
    

Problem conclusion

  • Upgrade to 11.1.4.7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT37394

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-06-23

  • Closed date

    2022-04-17

  • Last modified date

    2022-04-17

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IT37395

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022