IBM Support

IT30341: PERFORMANCE ISSUE DUE TO THE HIGH NUMBER OF ANCHOR LATCH CONTENTION

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • High number of anchor latch waiters can be observed on a highly
    OLTP environment which may impact query performance and cause
    performance bottlenecks.
    
    The anchor latch contention is caused during user privilege
    lookup when privileges are granted via user roles.
    
    We can see the following anchor latch waits in db2pd -latches
    outputs:
    
    Latch waiters with holders:
    ================================
    0_2019-08-23-08.03.44.074717: Latch Address: 0x0A0003284CD67584
    ( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
    , Num Waiters - 44,HOLDER EDU(s):  95813( 44 ),
    0_2019-08-23-08.06.03.094406: Latch Address: 0x0A0003284CD67584
    ( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 3
    , Num Waiters - 210,HOLDER EDU(s):  311661( 70 ), 91307( 70 ),
    67028( 70 ),
    0_2019-08-23-08.10.40.118186: Latch Address: 0x0A0003284CD67214
    ( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
    , Num Waiters - 19,HOLDER EDU(s):  141652( 19 ),
    0_2019-08-23-08.15.19.130377: Latch Address: 0x0A0003284CD67584
    ( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
    , Num Waiters - 95,HOLDER EDU(s):  222325( 95 ),
    0_2019-08-23-08.18.48.079977: Latch Address: 0x0A0003284CD67584
    ( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
    , Num Waiters - 91,HOLDER EDU(s):  456299( 91 ),
    
    
    
    SQLO_LT_sqlrlc_anchor_common__anchor_latch
    0x0A0003284A4D7584 0          615936     sqlbchsb.C           74
    SQLO_LT_sqlrlc_anchor_common__anchor_latch
    0x0A0003284A4D7584 0          632361     sqlbchsb.C           74
    SQLO_LT_sqlrlc_anchor_common__anchor_latch
    0x0A0003284A4D7584 0          650582     sqlbchsb.C           74
    SQLO_LT_sqlrlc_anchor_common__anchor_latch
    
    stack dumps collected during the issue may look as follows:
    
    0x0900000014A8C5C0 sqloXlatchConflict + 0x8A0
      0x09000000162B2F08
    sqlrlc_find_latch_anchor__FP8sqlrr_cbP10sqlrlc_keyPP20sqlrlc_anc
    hor_commonPP19sqlrlc_entry_commonT4Pb + 0x388
      0x09000000162BDBE8
    sqlrlc_auth_find_insert__FP8sqlrr_cbP21sqlrlc_auths_requiredP14S
    QLP_LOCK_INFOPb + 0xE8
      0x0900000016C42090
    sqlrlc_rtn_get_auths__FP8sqlrr_cbP21sqlrlc_auths_requiredUiPUcN2
    4 + 0x130
      0x0900000016C3E990
    sqlrlc_rtn_request_auths__FP8sqlrr_cbP11sqlr_aainfoPUcUsT3sUcT3T
    4T3T6N33P10sqlr_rolesbT16_ + 0x710
      0x0900000015BF4244
    sqlra_is_rtn_auth_held__FP8sqlrr_cbP18sqlra_routine_infoP10sqlr_
    rolesPUi + 0x104
      0x0900000015BF5998
    sqlra_compare_env_routine_auths__FP8sqlrr_cbP16sqlra_cached_envi
    T3Pi + 0x1578
      0x0900000015BC7444
    sqlra_hash_loc_env__FP8sqlrr_cbP14sqlra_cmpl_envP17sqlra_cached_
    stmtibPP16sqlra_cached_envT6PiPUl + 0x1364
      0x0900000015BD0270
    sqlra_find_var_env__FP8sqlrr_cbP17sqlra_anchor_stmtP17sqlra_cach
    ed_stmtP13sqlra_stmt_idUiP14sqlra_cmpl_env15sqlra_fill_modebT8Pi
    T10_PP16sqlra_cached_envT10_PUl + 0x1D0
      0x0900000015BDE11C
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
    T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
    SQLP_LOCK_INFOPP16sqlra_cached_varT12_bT19_Pb + 0x75C
      0x0900000016C9EAAC sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0xC0C
      0x0900000017AEDC50
    sqlrr_process_execute_request__FP8sqlrr_cbib + 0x42B0
      0x09000000163ED4E0
    sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0x5E0
      0x090000001B82F43C
    sqljs_ddm_excsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x5BC
      0x090000001B7B3D60
    sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
    interface + 0x280
      0x090000001B7B6DE4
    sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb +
    0x2264
      0x090000001B78339C
    IPRA.$sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0xE9C
      0x090000001B78BE94
    sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xB4
      0x090000001B781460
    IPRA.$sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x13E0
      0x090000001B77E8B4 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
    0x334
    
    
    
    Stack:
    ======================
    
    0x0900000014A8C958 sqloSpinLockReleaseConflict + 0xB8
      0x09000000162BF958
    sqlrlc_auth_find_insert__FP8sqlrr_cbP21sqlrlc_auths_requiredP14S
    QLP_LOCK_INFOPb + 0x1E58
      0x0900000016C42090
    sqlrlc_rtn_get_auths__FP8sqlrr_cbP21sqlrlc_auths_requiredUiPUcN2
    4 + 0x130
      0x0900000016C3E990
    sqlrlc_rtn_request_auths__FP8sqlrr_cbP11sqlr_aainfoPUcUsT3sUcT3T
    4T3T6N33P10sqlr_rolesbT16_ + 0x710
      0x0900000015BF4244
    sqlra_is_rtn_auth_held__FP8sqlrr_cbP18sqlra_routine_infoP10sqlr_
    rolesPUi + 0x104
      0x0900000015BF5998
    sqlra_compare_env_routine_auths__FP8sqlrr_cbP16sqlra_cached_envi
    T3Pi + 0x1578
      0x0900000015BC7444
    sqlra_hash_loc_env__FP8sqlrr_cbP14sqlra_cmpl_envP17sqlra_cached_
    stmtibPP16sqlra_cached_envT6PiPUl + 0x1364
      0x0900000015BD0270
    sqlra_find_var_env__FP8sqlrr_cbP17sqlra_anchor_stmtP17sqlra_cach
    ed_stmtP13sqlra_stmt_idUiP14sqlra_cmpl_env15sqlra_fill_modebT8Pi
    T10_PP16sqlra_cached_envT10_PUl + 0x1D0
      0x0900000015BDE11C
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
    T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
    SQLP_LOCK_INFOPP16sqlra_cached_varT12_bT19_Pb + 0x75C
      0x0900000016C9EAAC sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0xC0C
      0x0900000017AEDC50
    sqlrr_process_execute_request__FP8sqlrr_cbib + 0x42B0
      0x09000000163ED4E0
    sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0x5E0
      0x090000001B82F43C
    sqljs_ddm_excsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x5BC
      0x090000001B7B3D60
    sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
    interface + 0x280
      0x090000001B7B6DE4
    sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb +
    0x2264
      0x090000001B78339C
    IPRA.$sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0xE9C
      0x090000001B78BE94
    sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xB4
      0x090000001B781460
    IPRA.$sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x13E0
      0x090000001B77E8B4 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
    0x334
      0x0900000014B3D1A0 RunEDU__8sqeAgentFv + 0xB60
    
    The issue is more apparent when a user has many roles.
    

Local fix

  • As a temp workaround, granting the execute privileges to user
    directly, avoids the lookup by role and avoids this issue.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users on db2 v11.1 Fixpack 4 and lower                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to First Fixed in DB2 v11.1 M4 FP5 or see local Fix. *
    ****************************************************************
    

Problem conclusion

  • First Fixed in DB2 v11.1 M4 FP5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT30341

  • 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

    2019-09-20

  • Closed date

    2019-10-03

  • Last modified date

    2019-10-03

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA50 PSY

       UP

  • RB10 PSY

       UP

  • RB50 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 October 2019