A fix is available
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
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