PM16020: NEW FEATURE TO ADJUST PARALLELISM REDUCTION

Obtain the fix for this APAR.

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 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.