DB2 - Problembeschreibung
Problem IC95027 | Status: Geschlossen |
Poor performance due to frequent memory operations during queries with 'LIKE' predicates. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
The problem happens when multiple applications are running simultaneously and they all have complex expressions used in LIKE predicates. The cause of the performance issue is related to frequent memory operations during the handling of the LIKE operation. Here is an example of a LIKE predicates that can trigger the problem: Column1 LIKE REPLACE (Column2, '*', '&') In this example, since the pattern being compared is the result of an expression (in this case, the result of the REPLACE function), it requires that some memory is used to compute the pattern for every row scanned. The handling of this memory operation is inefficient, leading to a slower query performance. When the problem happens, please issue "db2pd -stack all" command to collect DB2 stack files. If the following stack traces can be found in the stack files, then it may suggest that this issue is occurring: =========================================== sqloSpinLockConflict SMemSet::captureLatch SQLO_MEM_POOL:allocateMemoryBlock sqlogmblkEx sqlmoAllocBlock sqlogmblkEx sqlri_alloc_pattern_mem sqlri_like_comp_pat <LatchInformation> Waiting on latch type: (SQLO_LT_SMemSet__MemLatchType__latch) </LatchInformation> Or sqloSpinLockConflict SMemSet::captureLatch sqlofmblkEx sqlri_free_pattern_mem sqlri_like_comp_pat <LatchInformation> Waiting on latch type: (SQLO_LT_SMemSet__MemLatchType__latch) </LatchInformation> =========================================== <StackTrace> MemChunksGet SMemSet::getChunksFromTree SMemSet:getContiguousChunks SQLO_MEM_POOL::allocateMemoryBlock sqlogmblkEx sqlmoAllocBlock sqlogmblkEx sqlri_alloc_pattern_mem sqlri_like_comp_pat </StackTrace> <LatchInformation> Holding Latch type: (SQLO_LT_SMemSet__MemLatchType__latch) </LatchInformation> Or <StackTrace> SMemSet::releaseLatch sqlofmblkEx sqlri_free_pattern_mem sqlri_like_comp_pat address: 0x00007FFFF558900B ; dladdress: 0x00007FFFF28F3000 ; offset in lib: 0x0000000002C9600B ; sqlri_like_comp_pat </StackTrace> <LatchInformation> Holding Latch type: (SQLO_LT_SMemSet__MemLatchType__latch) </LatchInformation> =========================================== | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users of version 9.7 on Linux, Unix and Windows * * platforms. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to DB2 LUW 9.7 Fix Pack 9 or higher levels. * **************************************************************** | |
Local-Fix: | |
Use constant strings in LIKE predicates, instead of complex compressions. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows | |
Lösung | |
First fixed in DB2 LUW Version 9.7 Fix Pack 9. | |
Workaround | |
Use constant strings in LIKE predicates, instead of complex compressions. | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC97012 IC97836 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 20.08.2013 06.01.2014 06.01.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP9 | |
Problem behoben lt. FixList in der Version | |
9.7.0.9 | |
9.7.0.9 |