IBM Support

IC72973: DB2 QUERY PERFORMANCE DEGRADATION WITH VERY LARGE SORTHEAP SETTING

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

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

Local fix

  • Update Sortheap setting to a lower value, such as 50000
    (requires setting SHEAPTHRES_SHR to a fixed value)
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users affected, especially larger machines where STMM    *
    * tunes sort                                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7 Fix Pack 5.                       *
    ****************************************************************
    

Problem conclusion

  • Problem first fixed in DB2 Version 9.7 Fix Pack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72973

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-12-02

  • Closed date

    2012-01-20

  • Last modified date

    2012-04-23

  • APAR is sysrouted FROM one or more of the following:

    IC72060

  • APAR is sysrouted TO one or more of the following:

Modules/Macros

  • sqri
    

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R950 PSN

       UP

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC72973

Modified date: 23 April 2012