IBM Support

IZ53209: THE OPTIMIZER MAY CHOOSE A LESS_THAN_OPTIOMAL ACCESS PLAN WHEN USING THE GREDDY JOIN ENUMERATION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The DB2 Query optimizer can use a faster method called Greedy
    for determining the order in which to join the tables referenced
    in the query when the query contains many join operations. This
    APAR is only applicable when using Greedy join enumeration. If
    the statement was compiled with query optimization levels 0, 1,
    or 2, or if you received a SQL0437W reason code 1 or reason code
    2, then the query was compiled using Greedy join enumeration.
    Additionally if you have the DB2_REDUCED_OPTIMIZATION registry
    variable set, then we can choose to use Greedy join enumeration
    under certain circumstances.
    
    The Greedy method favours the joins with the lowest cost and
    does not consider higher cost alternatives which may lead to a
    better performance. When the query contains many tables being
    joined with many transitive join predicates this enumeration
    method can lead to a less-than-optimal plan selection. For
    example in the following query
    
    SELECT  a1, a2, a3, b2, b3
    FROM    A,B,C,D
    WHERE a1=b1
    AND b1=c1
    AND d2=a2
    
    In this query there is an implicit join predicate a1 = c1. As a
    result the optimizer could consider an initial join of table A
    to any of B or C or D. If the join of A and B is the least
    expensive join it will be chosen using this faster join planning
    method, and once this is chosen we will no longer consider a
    join of A with C or A with D even though those might lead to a
    lower cost plan overall. For example if the join of A with B
    results in 1,000,000 rows but is very inexpensive and the join A
    with D result in 100 rows but it very expensive, the faster join
    planning method will chose the join A with B even though that
    means processing a very large number of rows.
    
    The problem addressed by this APAR is most often characterized
    by a series of joins using only the predicates 'a1=b1 and a1=c1
    and b1=c1' with rapidly increasing cardinality estimates shown
    in the plan. Eventually the join to table D will occur and the
    cardinality estimate will drop.
    
    This APAR will enable the optimizer to choose a better join
    order under the specified conditions. The behaviour change is
    enabled using either of the following settings:
    
    The setting
    
    DB2_EXTENDED_OPTIMIZATION=GY_DELAY_EXPAND
    
    Causes the faster join planning method to delay adding expanding
    joins until all the filtering and lookup joins have been added
    to the plan. In practical terms this means that the intermediate
    results sizes of the joins will not grow beyond the largest
    table involved in the join until the optimizer has no other
    choice. This prevent us from generating the huge intermediate
    result set, but the intermediate result set is still the size of
    the largest table, until it becomes cheapest to join in the
    filtering table(s).
    The risk of this setting, since planning is not strictly based
    on cost estimates, is that we are limiting our choices and thus
    may be forced into more expensive joins.
    
    or
    
    DB2_EXTENDED_OPTIMIZATION=GY_DELAY_LOOKUP
    
    Causes the greedy join enumeration algorithm to delay adding
    expanding and lookup joins until all the filtering joins have
    been added to the plan. What this means is that we will try to
    generate a plan that does the filtering as early as possible,
    thus shrinking the result set early.
    The risk of this setting, since planning is not strictly based
    on cost estimates, is that we are limiting our choices and thus
    may be forced into more expensive joins. The GY_DELAY_LOOKUP
    permits fewer choices then the GY_DELAY_EXPAND mode.
    

Local fix

  • If you are experiencing this problem you can work around the
    problem using the following methods:
    1) Use an optimization profile to indicate the first join should
    be between A and D
    
    2) If you are using query optimization level 0, 1 or 2, then
    attempt to compile the statement using optimization level 3 or
    higher so that we use the Dynamic Programming join enumeration
    method.
    
    3) If you have received a SQL0437W reason code 1 then attempt to
    increase the value of the STMTHEAP configuration parameter to
    give more memory to Dynamic Programming join enumeration.
    
    4) If you have DB2_REDUCED_OPTIMIZATION set then you may want to
    attempt to unset and determine if that helps the problem.
    

Problem summary

  • USERS AFFECTED
    
    All
    
    PROBLEM DESCRIPTION
    
    see ERROR DESCRIPTION
    
    PROBLEM SUMMARY
    
    see ERROR DESCRIPTION
    

Problem conclusion

  • The complete fix for this problem first appears in DB2 UDB
    Version 9.1 FixPak 9.
    

Temporary fix

  • If you are experiencing this problem you can work around the
    
    problem using the following methods:
    1) Use an optimization profile to indicate the first join should
    be between A and D
    
    2) If you are using query optimization level 0, 1 or 2, then
    attempt to compile the statement using optimization level 3 or
    higher so that we use the Dynamic Programming join enumeration
    method.
    
    3) If you have received a SQL0437W reason code 1 then attempt to
    increase the value of the STMTHEAP configuration parameter to
    give more memory to Dynamic Programming join enumeration.
    
    4) If you have DB2_REDUCED_OPTIMIZATION set then you may want to
    attempt to unset and determine if that helps the problem.
    

Comments

APAR Information

  • APAR number

    IZ53209

  • Reported component name

    DB2 UDB ESE SOL

  • Reported component ID

    5765F4102

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-06-12

  • Closed date

    2010-04-14

  • Last modified date

    2010-04-14

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

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

    IZ53476 IC61618

Fix information

  • Fixed component name

    DB2 UDB ESE SOL

  • Fixed component ID

    5765F4102

Applicable component levels

  • R810 PSN

       UP

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
14 April 2010