home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC72972 Status: Geschlossen

DB2 QUERY PERFORMANCE DEGRADATION WITH VERY LARGE SORTHEAP SETTING

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
Queries which are optimized to perform an IXAND operation may 
experience performance degradation given both of the following 
conditions: 
 
1. SORTHEAP is set very large (may be tuned by STMM).  The 
higher the value, the greater the performance impact. 
 
2. The Optimizer underestimates the number of input rows for the 
IXAND operation.  This second condition is difficult to detect, 
as it requires explain output as well as some analysis of the 
estimated vs. actual rows input. 
 
During an IXAND (Index-And) operation, if the input rows are 
significantly underestimated, additional memory will be required 
to efficiently execute the operation. Initially the additional 
memory allocations will be incremental and small, but after 
several increments, DB2 proceeds to allocate the maximum 
sortheap. 
There is high overhead in allocating the maximum sortheap for 
this specific IXAND operation, as much of the memory will likely 
be wasted. 
 
This APAR will modify the incremental memory allocation strategy 
in order to avoid the unnecessary maximum SORTHEAP allocation. 
This only impacts the case where optimizer estimates are 
inaccurate, eg. due to stale statistics, in which case the 
initial memory allocation is insufficient. 
 
Cases of severe degradation may be identified with stack 
tracebacks similar to the following : 
commitMemory 
getChunksFromTree 
getContiguousChunks 
getNewChunkSubgroup 
allocateMemoryBlock 
sqlogmblkEx 
sqlriCreateBloomFilter 
sqlriIncreaseBuildFilter 
sqlri_BFBandP 
 
In addition to performance degradation, the large single sort 
memory requests may both trigger and fail due to memory 
fragmentation. 
 
db2diag.log entries may show the failing memory allocation 
request matching the current sortheap configuration which is 
atypical: 
 
EDUID   : 5398                 EDUNAME: db2stmm (SAMPLE) 0 
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, 
probe:20 
CHANGE  : STMM CFG DB PR0: "Sortheap" From: "233043" <automatic> 
To: "246908" <automatic> 
 
In this case, the current configuration, 246908 4K pages, is 
very close to the failing allocation size of 1010675712 bytes. 
 
EDUID   : 111807               EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, SQO Memory Management, 
sqloMemLogPoolConditions, probe:30 
DATA #1 : <preformatted> 
Out of memory failure for Shared Sort Heap (SHEAPTHRES_SHR) on 
node 0. 
Requested block size           : 1010675712 bytes. 
Physical heap size             : 3062038528 bytes. 
Configured heap size           : 5056757760 bytes. 
Unreserved memory used by heap : 0 bytes. 
Unreserved memory left in set  : 11337531392 bytes.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Systems with large SORTHEAP settings (may be tuned high by   * 
* STMM)                                                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5 Fix Pack 8 or higher OR manually  * 
* set SORTHEAP to a lower fixed value.                         * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
Problem first fixed in DB2 Version 9.5 Fix Pack 8
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
02.12.2010
12.08.2011
26.01.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.FP8
Problem behoben lt. FixList in der Version
9.5.0.8 FixList