IC80950: SORTS FROM SMALL INDEX BUILDS OVER-RESERVE SORT MEMORY, STMM MAY UNNECESSARILY INCREASE SORT TUNING

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • Index builds, including during reorg, reserve the full sortheap
    from sort memory.  Most index builds are done in parallel with 6
    subagents, so most reserve 6 full sortheaps.  Much of this
    memory may never be allocated for smaller index builds.  Since
    STMM tuning is based on the sort reservation, STMM may react by
    increasing the sort configuration, which may cause a subsequent
    index build to reserve even more memory.
    
    Generally there is no adverse symptom from this other than an
    STMM-tuned configuration may seem to set SHEAPTHRES_SHR and
    SORTHEAP unnecessarily high.  In extreme cases other STMM
    consumers may be decreased, causing some performance
    degradation.
    
    One method of determining this issue exists is by examining a
    database snapshot.  The high water mark for reserved shared sort
    usage:
    Shared Sort heap high water mark           = 70129 ( 4K pages )
    
    May be much higher than the actual shared sort pool usage :
        Memory Pool Type                           = Shared Sort
    Heap
           Current size (bytes)                    = 0
           High water mark (bytes)                 = 20512768
           Configured size (bytes)                 = 307200000
    
    In this case, the highest reserved sort memory was 280516MB, but
    the actual allocated memory only reached 20MB.
    

Local fix

  • configure sortheap, sheapthres_shr to fixed values
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update Sortheap, SHEAPTHRES_SHR configuration parameters to  *
    * fixed values if a performance impact is observed.            *
    ****************************************************************
    

Problem conclusion

  • Problem first fixed in DB2 Version 9.7 Fix Pack 8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC80950

  • 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

    2012-01-20

  • Closed date

    2013-07-10

  • Last modified date

    2013-07-10

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

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

    IC93301 IC99068

Modules/Macros

  • sqs
    

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC80950

Modified date:

2013-07-10

Translate my page

Machine Translation

Content navigation