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. |
