DB2 - Problem description
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) |