PH27330: SLOW SQL PERFORMANCE CAN OCCUR WHEN FETCH FIRST N ROWS ONLY OR OPTIMIZE FOR N ROWS IS SPECIFIED AND THERE ARE NO STATS ON TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Slow SQL performance can occur for SQL containing FETCH FIRST
  n ROWS ONLY or OPTIMIZE FOR n ROWS, and there are no
  statistics collected on the object.  The slow performance can
  occur when Db2 chooses list prefetch and the filter factor
  uncertainty is very high.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * For all Db2 for z/OS V12 users who have                      *
  * single-table queries qualified by all of                     *
  * the following conditions.                                    *
  * 1.Query has either OPTIMIZE FOR N ROWS or                    *
  * FETCH FIRST N ROWS,                                          *
  * 2.The table or index being accessed has                      *
  * no statistics.                                               *
  * 3.The matching predicate of index access                     *
  * has high cost uncertainty, which usually                     *
  * comes from matching with RANGE or BETWEEN                    *
  * predicates.                                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * For the query pattern that satisfies                         *
  * the above conditions, Db2 may select                         *
  * index access with list prefetch. This                        *
  * may not have the expected performance                        *
  * as index scan non-list prefetch plan.                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  The non-optimal access path is largely caused by statistics not
  being collected. This APAR identified such cases and allow
  Db2 to avoid list prefetch plan when direct access plan exists.
  It is still recommended to gather statistics on the affected
  objects when possible to improve Db2's ability to select
  a good access path.
  Additional Keywords:
  SQLACCESSPATH SQLFFNR SQLOFNR LISTPREFETCH
  

Problem conclusion

• The described problem is fixed.
  

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:

  UI71312

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI71312

     UP20/09/04 P F009

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.