|
Problem IT24829
|
Status: Closed |
LONG DURATION OF HOLDING OF THE Statement ANCHOR LATCH CAUSED LATCH CONTENTION
|
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
In some cases, with high concurrency of dynamic SQL statements,
there can be latch contention in the package cache.
The latch contention is more likely with the database
configuration parameter stmt_conc=literals.
The contention can look like the following:
db2pd -latches output:
Latches:
Address Holder Waiter Filename
LOC LatchType HoldCount
...
0x0A00030EFA541A58 57569 224007 sqlra_dyn_cache.C 96
SQLO_LT_sqlra_anchor_stmt__latch 1
0x0A00030EFA541A58 57569 326812 sqlra_dyn_cache.C 96
SQLO_LT_sqlra_anchor_stmt__latch 1
0x0A00030EFA541A58 57569 328611 sqlra_dyn_cache.C 96
SQLO_LT_sqlra_anchor_stmt__latch 1
0x0A00030EFA541A58 57569 415478 sqlra_dyn_cache.C 96
SQLO_LT_sqlra_anchor_stmt__latch 1
...
The holder of the anchor latch is waiting for package cache
memory pool latch.
0x0A00030EFA4B2F30 415850 57569
/view/db2_v105fp5_aix64_special_33943_special_34714/vbs/engn/inc
lude/sqlo_mem_latch.h 63
SQLO_LT_SMemPool__MemLatchType__latch 1
0x0A00030EFA4B2F30 415850 170576
/view/db2_v105fp5_aix64_special_33943_special_34714/vbs/engn/inc
lude/sqlo_mem_latch.h 63
SQLO_LT_SMemPool__MemLatchType__latch 1
0x0A00030EFA4B2F30 415850 178800
/view/db2_v105fp5_aix64_special_33943_special_34714/vbs/engn/inc
lude/sqlo_mem_latch.h 63
SQLO_LT_SMemPool__MemLatchType__latch 1
0x0A00030EFA4B2F30 415850 192421
/view/db2_v105fp5_aix64_special_33943_special_34714/vbs/engn/inc
lude/sqlo_mem_latch.h 63
SQLO_LT_SMemPool__MemLatchType__latch 1
0x0A00030EFA4B2F30 415850 228633
/view/db2_v105fp5_aix64_special_33943_special_34714/vbs/engn/inc
lude/sqlo_mem_latch.h 63
SQLO_LT_SMemPool__MemLatchType__latch 1
0x0A00030EFA4B2F30 415850 241483
/view/db2_v105fp5_aix64_special_33943_special_34714/vbs/engn/inc
lude/sqlo_mem_latch.h 63
SQLO_LT_SMemPool__MemLatchType__latch 1
The stack of the EDU holding this package cache pool latch:
0x090000000A15FF7C @87@MemTreeInsert__FP8SMemNodePP8SMemNode +
0x80
0x090000000A161D34 releaseLatch__13SQLO_MEM_POOLFv + 0x4
0x090000000A15F868 sqlofmblkEx + 0x270
0x0900000009BC2498
sqlra_hash_del_stmt__FP13SQLO_MEM_POOLP17sqlra_anchor_stmtP17sql
ra_cached_stmtPi + 0x39C
0x0900000009BC25DC
sqlra_cache_del_empty_stmt__FP8sqlrr_cbP17sqlra_anchor_stmtP17sq
lra_cached_stmtPi + 0x88
0x0900000009BCA844
sqlra_csm_dec_and_clean_statement__FP8sqlrr_cbP17sqlra_cached_st
mt + 0x2C8
0x090000000BF41F10
sqlra_sqlC_free_section__FP8sqlrr_cbP25sqlra_sql_context_sibling
i@OL@21253 + 0x54
0x090000000AAFD2AC
sqlra_sqlC_free_section__FP8sqlrr_cbP25sqlra_sql_context_sibling
i + 0x254
0x090000000B69BA3C
sqlra_sqlC_get_stmt__FP8sqlrr_cbPUcUiUciP17sqlra_cached_stmtPi +
0x6C8
0x090000000AC8FD58
sqlra_get_section__FP8sqlrr_cbPUcsT2T3T2UsUlP16db2UCprepareInfoP
15db2UCCursorInfoiUi + 0x1FC
0x090000000AC88F58
sqlrr_sql_request_pre__FP14db2UCinterfaceUiiP16db2UCprepareInfoP
15db2UCCursorInfo + 0x2E4
0x090000000AC17BC0
sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0xB38
0x090000000AB2E0A0
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0x824C
As a relief of the latch contention like above, this APAR will
reduce the duration of holding of the anchor latch during the
removal of single statements from the cache. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* N/A *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 V10.5 FP10 or later. *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 24.04.2018
11.07.2018
11.07.2018 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
