IBM Support

IT17130: SORT RESERVATION MAY BE LEAKED UNDER CONSTRAINED SORT TUNING CONDITIONS, LEADING TO PERFORMANCE DEGRADATION AND SQL0955 ERRORS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When SORTHEAP is constrained, sort reservation may be leaked
    during the dynamic acquisition of sort memory (known as
    incremental sort).  This occurs when the additional available
    reservation amount is small enough that it cannot be effectively
    utilized by the sort operator.  The reservation is acquired then
    abandoned, the result being that the reservation is never
    released while the database remains active.
    
    Over time the leaked reservation amount builds to the point
    where the total sort reservation exceeds the configured
    SHEAPTHRES_SHR size.  At this point, sort reservation requests
    start being heavily throttled (post-threshold sorts), resulting
    in queries acquiring less memory for execution, which leads to
    performance degradation.  When the total sort reservation
    reaches 1.25 x SHEAPTHRES_SHR, queries with larger requirements
    will start failing with SQL0955.
    
    Notes:
    - constrained sortheap can be a result of many factors,
    including STMM tuning, overall memory available or configured
    for the database, and activity.  For example, if there is a
    sudden spike in activity requiring sort memory and sort is
    configured conservatively by STMM, it may take some time for
    STMM to adjust the configuration, before which the sort
    configuration may be temporarily constrained.
    - the leaked reservation is not real memory, it is only an
    accounting value used to guide sort memory utilization.
    - use of intra-parallelism may increase the likelihood of
    encountering the problem due to apportioning the SORTHEAP
    maximum across multiple agents
    
    The problem can be detected by observing unexpectedly high sort
    reservation levels.  For example :
    1. during very low activity levels, the sort reservation appears
    very high, potentially exceeding SHEAPTHRES_SHR
    2. sort reservation amounts far exceed the actual sort memory
    usage levels.
    
    To determine the sort memory reservation levels, check the
    SORT_SHRHEAP_ALLOCATED in the MON_GET_DATABASE routine, or the
    "Total Shared Sort heap allocated" amount in a database
    snapshot.  Both values are in 4K pages.  Compare this to the
    actual shared sort memory usage - MEMORY_POOL_USAGE for the
    SHARED_SORT pool type in the MON_GET_MEMORY_POOL routine (value
    is in 1K units) :
    eg.
    
    db2 select SORT_SHRHEAP_ALLOCATED from
    table"(MON_GET_DATABASE(null))"
    
    db2 select MEMORY_POOL_TYPE, MEMORY_POOL_USED from
    table"(MON_GET_MEMORY_POOL('DATABASE',null,null))" where
    MEMORY_POOL_TYPE = "'SHARED_SORT'"
    

Local fix

  • Applying an appropriate fixed SORTHEAP/SHEAPTHRES_SHR tuning
    will avoid the problem.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All systems may be affected                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 11.1 Mod 1 Fix Pack 1.                *
    ****************************************************************
    

Problem conclusion

  • Problem first fixed in DB2 Version 11.1 Mod 1 Fix Pack 1
    

Temporary fix

  • See Local Fix
    

Comments

APAR Information

  • APAR number

    IT17130

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-09-20

  • Closed date

    2017-10-10

  • Last modified date

    2017-10-10

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

    IT16970

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

Modules/Macros

  • sqs
    

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020