PI07533: POOR PERFORMING ACCESS PATH FOR QUERY WITH MANY JOINS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• For the original case, the customer had a many join query that
  included many left outer joins.  In V10, they lost a needed
  multiple index access path to a path to one that the optimizer
  knows it is bad.  However, the optimizer clipped the good path.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 10 and DB2 11 for z/OS users of          *
  *                 joins with 3 or more tables.                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 may select an inefficient access    *
  *                      path for a query with a join of 3 or    *
  *                      more tables even though there is        *
  *                      another access path whose cost is       *
  *                      cheaper.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may select an inefficient access path for a query with a
  join of 3 or more tables even though there is another access
  path whose cost is cheaper.
  
  The cheaper access path may not be chosen because it is clipped
  during the optimization process due to a storage restriction.
  The maximum amount of storage which can be used by the optimizer
  is defined by the subsystem parameter MAX_OPT_STOR.  When the
  storage used by the optimizer exceeds MAX_OPT_STOR, DB2 will
  attempt to restrict the number of join permutations considered,
  which can cause the potential good access path to be missed.
  
  Another symptom of the storage restriction is that an efficient
  multi-index access plan is missed.
  
  Another possible symptom is ABEND0C4 RC38 DSNXOGP OFFSET99E0,
  which can be resolved by this APAR.
  
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLSTORAGE SQLMIDX MIDX
  

Problem conclusion

• DB2 is enhanced to count the storage used by specific optimizer
  components only. Before the change, the storage used by some
  components executing before the optimizer were counted too.
  
  Users may notice that STOR_USED in DSN_DETCOST_TABLE is
  reduced significantly after the APAR is applied.
  
  Users may also notice total storage consumption increase during
  BIND, REBIND, PREPARE and EXPLAIN after the APAR is applied.
  The storage will be freed immediately after the BIND, REBIND,
  PREPARE and EXPLAIN. The extra storage usage allows the
  optimizer to consider more join permutations to improve the
  chances of finding the optimal access path.  Storage usage
  by optimizer components will remain bounded by the
  MAX_OPT_STOR threshold, but the total storage usage could
  exceed it.
  

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:

  UI15763 UI15764

Modules/Macros

• DSNXOCU  DSNXOD4  DSNXOD7  DSNXOGCM DSNXOGP  DSNXOMPS DSNXOMPU
  DSNXOPEK DSNXOPOP DSNXOPSH DSNXOSUD DSNXOTS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI15763

     UP14/03/21 P F403

• RB10 PSY UI15764

     UP14/03/21 P F403

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.