PH45369: PERFORMANCE ISSUE WHEN SPARSE INDEX WAS CHOSEN BUT IT SPILLS INTO PHYSICAL WORKFILE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A performance degradation (in query execution time) is possible
  when the result of a sparse index is directed to a physical
  workfile.  This can create an excessive amount of GetPages and
  CPU when sparse index is accessed.
  
  When workfile is bigger than the sparse index pool, the sparse
  index performance suffers from the big workfile access cost.
  Internally, the cost penalty to the sparse index access needs
  adjustment when the workfile size is too big.
  Db2 should skip building the sparse index when the following
  conditions are met.
  - The workfile size, 10 times larger than the total sparse
  index pool size.
  - The outer cardinality is comparable to the inner table's
  ONECOMPROWS (DSN_DETCOST_TABLE). The inner table's ONECOMPROWS
  is larger than 10K.
  - There exists an index plan on the inner table that covers
  all sparse index keys and more.
  
  When the above conditions are met, the index plan can provide
  the same or better predicate coverage. Db2 will skip building
  the sparse index access to avoid huge workfile access cost
  related to the sparse index access.
  
  ADDITIONAL SYMPTOMS:
  SQLPERFORMANCE PERFM SQLSPARSEINDEX SPARSEINDEX
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS running                         *
  * queries referencing big tables and                           *
  * using sparse indexes.                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A performance degradation (in query                          *
  * execution time) may occur when the                           *
  * following conditions are met.                                *
  * (1) The PLAN_TABLE.PRIMARY_ACCESSTYP                         *
  * has the value 'T' for a table, meaning                       *
  * the sparse index is used and,                                *
  * (2) The sparse index size in (1) is                          *
  * bigger than the sparse index pool                            *
  * size.                                                        *
  * When the conditions above are met,                           *
  * the sparse index can spill to                                *
  * workfiles. It can cause excessive                            *
  * GetPages and CPU. Therefore it will                          *
  * lead to suboptimal performance.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  A performance degradation (in query execution time) may occur
  when the following conditions are met.
  (1) The PLAN_TABLE.PRIMARY_ACCESSTYP has the value 'T' for a
  table, meaning the sparse index is used and,
  (2) The sparse index size in (1) is bigger than the sparse
  index pool size.
  When the conditions above are met, the sparse index can spill
  to workfiles. It can cause excessive GetPages and CPU.
  Therefore it will lead to suboptimal performance.
  SQLSPARSEINDEX SPARSEINDEX SQLACCESSPATH SQLPERFORMANCE
  

Problem conclusion

• PH45369 will solve the sparse index performance problem
  described above by detecting the following conditions and
  skip using the sparse index.
  (1) The sparse index size is bigger than the sparse index
  pool and,
  (2) The inner table size estimate after applying all the
  local predicates is bigger than 10K and,
  (3) There exists an index that can cover all the spasre index
  keys.
  Please refer to PH46618 for the v13 fix.
  

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:

  PH46618 UI81024

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI81024

     UP22/08/06 P F208

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.