IBM Support

IC88502: INACCURATE DENSITY AND SEQUENTIAL_PAGES STATISTICS FOR PARTITIONED INDEXES MIGHT LEAD TO NON-OPTIMAL ACCESS PLANS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • DENSITY for partitioned indexes might be inaccurate when the
    partitioned table has a significant number of empty partitions.
    
    SEQUENTIAL_PAGES for partitioned indexes might be inaccurate and
    inconsistent with NLEAF when the partitioned table has a
    significant number of empty or near empty partitions.
    

Local fix

  • 1. Use optimization guidelines to direct the query optimizer to
    different access plans.
    
    2. Alternately, provide the query optimizer with more accurate
    statistics via manually updating the relevant SYSSTAT.INDEXES
    rows.  To determine what statistical values to use, one approach
    is to examine the per-partition index statistics in
    SYSCAT.INDEXESPARTITIONS and use a value representative from the
    majority of partitions with data.  You can use the NUMRIDS
    statistics to assess if a partition is empty or near empty.
    When updating SEQUENTIAL_PAGES, a further consideration is its
    relationship to NLEAF.  For example, if the majority of data
    holding partitions have
    SYSCAT.INDEXESPARTITIONS.SEQUENTIAL_PAGES near
    SYSCAT.INDEXESPARTITIONS.NLEAF, then update
    SYSTAT.INDEXES.SEQUENTIAL_PAGES so that it is near
    SYSTAT.INDEXES.NLEAF.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users who use ranged partitioned table and when its density  *
    * is very low                                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * DENSITY for partitioned indexes might be inaccurate when the *
    * partitioned table has a significant number of empty          *
    * partitions.                                                  *
    *                                                              *
    * SEQUENTIAL_PAGES for partitioned indexes might be inaccurate *
    * and                                                          *
    * inconsistent with NLEAF when the partitioned table has a     *
    * significant number of empty or near empty partitions.        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to DB2 version 9.7 fixpack 8                          *
    ****************************************************************
    

Problem conclusion

Temporary fix

  • 1. Use optimization guidelines to direct the query optimizer to
    different access plans.
    

Comments

APAR Information

  • APAR number

    IC88502

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-22

  • Closed date

    2013-04-02

  • Last modified date

    2013-04-02

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

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

    IC91223

Fix information

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC88502

Modified date: 02 April 2013