|
Problem IT20257
|
Status: Closed |
DB2 MAY SHOW INCREASED LATCHING WHEN PLANS WITH HASHJOIN PRODUCENO ROWS ON BUILD SIDE |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
When the problem happens, stack analysis shows many threads are
contending on SQLO_LT_SMemPool__MemLatchType__latch when doing
hash join operation.
******** LATCHWAIT DETECTED ( #3 ) ***********
Printing LatchWait information
=========================================
<<<< Holder Information (Address = 0xa00020000006298) >>>>
File Information:
Line: 63, File:
/view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
h HoldCount: 1
----------------------------
PID(s):
71095 (/home/db2v101/work/stack/8192462.71095.000.stack.txt)
-- Line: 63, File:
/view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
h HoldCount: 1 (SQLO_LT_SMemPool__MemLatchType__latch)
Agent Type: db2agent (EBANKCB)
*** Stack ***
Timestamp: 2017-03-27-10.23.52.180430
0x0900000012EA11D8 sqloXlatchConflict + 0x5F0
0x090000001228D358 captureLatch__7SMemSetFv + 0x23C
0x090000001228DC84 sqloCreateMemorySubPool + 0x150
0x090000001228DA84
sqlriCreateMemoryPool__FP8sqeAgentPP13SQLO_MEM_POOLlN23P19SqloRe
sourceBindingP19STMM_OpAllocMonitor + 0x60
0x09000000122820AC sqlri_hsjnInit__FP8sqlrr_cb + 0x8DC
0x0900000012FDB1D8 sqlrievl__FP8sqlrr_cb + 0x10
0x090000000EB4A6AC glue_pointer_func@glueD + 0x84
0x0900000012D81DF0 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
0x2AC
0x0900000012A29000
sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
0x0900000012A29000
sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
0x0900000012A433EC
sqlrr_open__FP14db2UCinterfaceP15db2UCCursorInfo + 0x270
0x09000000130C63B8
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0xD04
0x09000000130BD8E0 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x11E4
0x09000000130BD8E0 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x11E4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~
<<<< Waiter Information (Address = 0xa00020000006298) >>>>
TOTAL WAITERS >> 202
File Information:
Line: 63, File:
/view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
h
----------------------------
PID(s):
100082
(/home/db2v101/work/stack/8192462.100082.000.stack.txt) -- Line:
63, File:
/view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
h (SQLO_LT_SMemPool__MemLatchType__latch)
Agent Type: db2agent (EBANKCB)
<<< Stack >>>
Timestamp: 2017-03-27-10.23.52.141402
0x09000000000F0994 thread_wait + 0x94
0x0900000012FA93AC getConflictComplex__17SQLO_SLATCH_CAS64FCUl +
0xA24
0x0900000012721DB4 captureLatch__13SQLO_MEM_POOLFv + 0x3C
0x090000001228D6EC sqloPurgeMemoryInSubPool + 0xD8
0x090000001228D5B4 sqloFreeMemorySubPool + 0x3C
0x090000001228D51C sqlriFreeMemoryPool__FP13SQLO_MEM_POOLl +
0x80
0x0900000012290F74 sqlri_hsjnClose__FP8sqlrr_cbP11sqlri_hsjno +
0x8CC
0x09000000124130F8 sqlrihsjn__FP8sqlrr_cb + 0x6D8
0x0900000012FDB1D8 sqlrievl__FP8sqlrr_cb + 0x10
0x090000000EB4A6AC glue_pointer_func@glueD + 0x84
0x0900000012D81DF0 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
0x2AC
0x0900000012A29000
sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
0x0900000012A29000
sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
0x0900000012A433EC
sqlrr_open__FP14db2UCinterfaceP15db2UCCursorInfo + 0x270
100596
(/home/db2v101/work/stack/8192462.100596.000.stack.txt) -- Line:
63, File:
/view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
h (SQLO_LT_SMemPool__MemLatchType__latch)
Agent Type: db2agent (EBANKCB)
<skipped>
From db2pd -mempool, this latch protects shsorth:
Memory Pools:
Address MemSet PoolName Id SecondId
Overhead LogSz LogHWM PhySz PhyHWM
CfgSize Bnd BlkCnt CfgParm
0x0A00020000006280 EBANKCB shsorth 18 0 51520
3460288 991428160 8912896 648740864 2147483648 No
179 SHEAPTHRES_SHR |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 10.5 Fix Pack 9 or higher *
**************************************************************** |
| Local Fix: |
rewrite SQL statement |
| Solution |
First fixed in Db2 10.5 Fix Pack 9 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.04.2017
29.09.2017
29.09.2017 |
| Problem solved at the following versions (IBM BugInfos) |
9.0.
|
| Problem solved according to the fixlist(s) of the following version(s) |
