IBM Support

PM16020: NEW FEATURE TO ADJUST PARALLELISM REDUCTION

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • n/a
    DB2PARALL/K
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS parallelism users           *
    *                 who are on V9.                               *
    ****************************************************************
    * PROBLEM DESCRIPTION: Poor query performance when a           *
    *                      suboptimal access path is chosen        *
    *                      when parallelism is enabled.            *
    *                                                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Prior to DB2 V9, DB2 chose the access path with the cheapest
    sequential cost then attempted to parallelize this access path.
    In DB2 V9, DB2 costs candidate access paths and preserves
    several access paths for parallelism consideration.  There
    is a parallelism-based cost reduction provided to the candidate
    access paths based on their ability to exploit parallelism.
    The ability to go parallel and the degree of parallelism for
    a particular access path can become a major factor in the
    access path being chosen.  DB2 can be overly optimistic
    when providing a cost reduction based on the effectiveness
    of parallelism.  This can cause DB2 to choose an access path
    which has higher processing cost, but parallelizes better.
    If the parallelism is not as effective as expected, the higher
    processing costs result in DB2 choosing an access path which
    has a higher processing cost without the expected parallelism
    elapsed time reduction.
    
    
    Additional Keywords:
    SQLCPUP  SQLPARALLELISM  SET CURRENT DEGREE ANY
    SQLPERFORMANCE  SQLACCESSPATH
    

Problem conclusion

  • This APAR introduces a new subsystem parameter, PARA_EFF.
    PARA_EFF is a parameter which DB2 uses to control how
    much cost reduction to provide based on parallelism.
    It controls DB2's parallelism efficiency assumption.
    
    
    1. Setting PARA_EFF:
    
    You can set PARA_EFF to a value from 0 to 100.
    Consider the value as a percentage from 0% to 100%.
    
    If you set PARA_EFF to 0, DB2 will choose the access
    path with the cheapest estimated sequential cost.
    This effectively restores pre-DB2 9 behavior.
    
    The default for PARA_EFF is 100.  DB2 will apply the
    same DB2 9 parallelism cost reduction as you would have
    without applying this fix.
    
    If you set PARA_EFF to a value between 1 and 99,
    this results in a less optimistic assumption regarding
    parallelism efficiency within DB2.  So DB2 will retain
    the DB2 9 behavior of allowing an access path which
    obtains (more) parallelism in the optimization decision,
    but the cost reduction is diluted.
    
    The closer PARA_EFF is to 100, the more aggressive
    DB2 will be at choosing an access path that has
    higher estimated processing costs to obtain an
    access path with more parallelism.
    
    The closer PARA_EFF is to 1, DB2 will still consider
    the parallelism reduction but it will have a
    proportionally reduced effect on the overall
    optimization choice.
    
    
    2. The following arbitrary example is for illustration purposes:
    
    Query 1 has access path candidates AP1 and AP2
    
    AccessPath sequential_cost parallel_degree parallel_reduced_cost
    ---------- --------------- --------------- ---------------------
    AP1        1000             5              400
    AP2        2000            20              300
    
    In this case, access path AP2 has 2x the cost of AP1's
    sequential access path estimated cost.  However, access
    path AP2 obtains a degree 4x as high as access path AP1
    (20 degrees versus 5 degrees).  With a full parallelism
    cost reduction, DB2 chooses an access path whose sequential
    cost is 2 times higher, but the benefit is a higher degree
    of parallelism and hopefully a lower elapsed time.
    
    If PARA_EFF is set to 50, the sequential costs for both
    AP1 and AP2 would be the same as before, but both parallel
    reduced costs would be higher.
    
    AccessPath sequential_cost parallel_degree parallel_reduced_cost
    ---------- --------------- --------------- ---------------------
    AP1        1000             5               700
    AP2        2000            20              1150
    
    With PARA_EFF of 50, the parallelism reduction for access path
    AP2 is no longer enough to compensate for the higher processing
    cost to result in a cheaper estimate than the parallel reduced
    cost for AP1.  Indeed, the parallel reduced cost for AP2 is
    higher than the sequential cost for access path AP1.  It is
    possible if AP2 were able to obtain 40, 50, or 60 degrees of
    parallelism and AP1 were stuck at 5 degrees of parallelism that
    the parallelism reduction might make AP2 more competitive, and
    DB2 might once again choose access path AP2 over access path
    AP1.
    
    
    3. Interactions with PARAMDEG:
    
    Queries which have a high degree of parallelism get a larger
    parallelism reduced cost.  The lower PARAMDEG, the lower the
    possible parallelism reduced cost (fewer degrees of parallelism
    means less parallelism reduced cost).  If PARAMDEG were set to
    10, access path AP2 above would not have been able to obtain 20
    degrees of parallelism.  It would not have obtained the initial
    parallel reduced cost that drove it to be chosen over access
    path AP1.  The higher the possible degree of parallelism,
    the lower PARA_EFF would need to be set to obtain a conservative
    choice.
    
    
    4. Recommendation:
    
    Without the fix, DB2's parallelism assumptions are too
    optimistic.  For an access path with a higher sequential cost
    to win over competing plans, it's sequential cost needs to be
    closer, or it's parallelism benefit great enough to win over
    the access paths it is competing against.  We recommend you
    use 50 for PARA_EFF.  For existing DB2 9 parallelism users,
    if by any chance there is performance regression after setting
    PARA_EFF to 50, switching it back to 100 will restore the
    original performance.
    
    If you are migrating from DB2 V8 and you do not want DB2 to
    consider parallelism reduction at all, you can set it to 0.
    This will allow for parallelizing of the winning sequential
    plan only, just as in V8.
    
    If you have been running with DB2 9 parallelism and are not
    experiencing access path regressions for parallel queries,
    you can retain existing behavior by keeping PARA_EFF at the
    default setting, which is 100.
    
    
    5. Why does this APAR contain a V8 change?
    
    Please note that although this is a V9 APAR, it does include
    a small V8 change. The APAR updates DSNADMIZ, the external
    module for the SYSPROC.ADMIN_INFO_SYSPARM stored procedure
    in DB2 V8 and V9, so that it can report the setting for the
    new PARA_EFF subsystem parameter. In DB2 data sharing, a
    SYSPROC.ADMIN_INFO_SYSPARM connection on any member of the
    group can query the subsystem, DECP and certain IRLM
    parameter settings of any other member of that group.
    In order to support V9 data sharing coexistence mode, the V8
    SYSPROC.ADMIN_INFO_SYSPARM must recognize the new PARA_EFF
    subsystem parameter.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM16020

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-06-07

  • Closed date

    2010-07-29

  • Last modified date

    2011-04-27

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

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

    UK59310 UK59309

Modules/Macros

  • DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIJUZ DSNTINST
    DSNWZIF9 DSNXMOPC DSNXOGP2 DSNXOMPS DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R810 PSY UK59309

       UP10/08/23 P F008

  • R910 PSY UK59310

       UP10/08/23 P F008

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
27 April 2011