IBM Support

IT34306: OPTIMIZER USES QUANTILE STATS FOR UNIQUE COLUMNS TO COMPUTE THE EQUALITY PREDICATE FILTER FACTOR WHICH LEADS TO OVER-ESTIMATION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • For unique columns frequent value statistics are not collected
    as it is guaranteed to only have 1 occurence for each distinct
    value.
    However, quantile stats are still available and the optimizer
    will try to use them as well to compute the equality predicate
    filter factor.
    This leads to the over-estimation. If a column is unique it can
    at most 1 row match any value.
    

Local fix

  • Modify the runstats command to collect distribution stats on all
    the columns except for the affected one.
    If using auto-runstats, set a statistic profile otherwise the
    next time auto-runstats collects stats on the table it will
    revert
    to the default of collect distribution stats on all columns.
    
    An alternative workaround is to use a variable instead of a
    constant value in the query for the predicate that is impacted.
    
    When local predicates have constant values, the optimizer will
    use the distribution stats when estimating the selectivity,
    but if there is an unknown value, they will not be used and this
    will avoid the issue for this case.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.7                                          *
    ****************************************************************
    

Problem conclusion

  • Upgrade to 11.1.4.7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT34306

  • 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

    2020-09-22

  • Closed date

    2022-04-17

  • Last modified date

    2022-04-17

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

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

    IT39185

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"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:
04 May 2022