IY75348: NEW DB2_REDUCED_OPTIMIZATION SETTING TO RESOLVE SLOW PERFORMANCE WITH OPTIMIZE FOR N ROWS DUE TO LOSS OF INDEX-ORING

DB2 UDB Version 8.1 FixPak 16 (also known as Version 8.2 FixPak 9 )

APAR status

• Closed as program error.

Error description

• A query may be slower when it includes the OPTIMIZE FOR n ROWS
  clause than when it does not include that clause.
  This may occur even if the query also includes the FETCH FIRST n
  ROWS clause, with the value used for n in both clauses being the
  same.
  
  This may occur if index-oring is used in the access plan of
  the query without the OPTIMIZE FOR n ROWS clause, but
  index-oring is not used for the query with that clause.
  This may happen because index-oring is not normally used in
  the access plan of any query that includes the OPTIMIZE FOR n
  ROWS clause.
  
  You can view the access plan for a query by using a tool such
  as db2exfmt. If the access plan includes a node labelled RIDSCN
  dominating more than one branch--each of which includes a node
  labeled IXSCAN, this shows that the query plan includes
  index-oring.
  
  This APAR addresses the issue by creating a new setting for the
  DB2_REDUCED_OPTIMIZATION DB2 UDB registry variable, which will
  prevent the slow performance in this case.  It does so by
  allowing index-oring to be used in the access plan of queries
  that include the OPTIMIZE FOR n ROWS clause.
  
  Instead of the DB2_REDUCED_OPTIMIZATION setting that this APAR
  introduced, it is recommended to use a new setting
  DB2_REDUCED_OPTIMIZATION=IXOROFNR that was
  introduced by APAR IY80102 (which is in DB2 version 8 fixpak 12
  and later) to prevent the slow performance in this case.
  
  To activate that setting, run
   db2set DB2_REDUCED_OPTIMIZATION=IXOROFNR
  and then restart DB2.
  

Local fix

• Omit the OPTIMIZE FOR n ROWS clause.
  

Problem summary

• Users affected:
  Users of DB2 version 8 on all Linux, UNIX and Windows operating
  systems.
  
  Problem Description:
  A query may be slower when it includes the OPTIMIZE FOR n ROWS
  clause than when it does not include that clause.
  This may occur even if the query also includes the FETCH FIRST n
  ROWS clause, with the value used for n in both clauses being the
  same.
  
  This may occur if index-oring is used in the access plan of
  the query without the OPTIMIZE FOR n ROWS clause, but
  index-oring is not used for the query with that clause.
  This may happen because index-oring is not normally used in
  the access plan of any query that includes the OPTIMIZE FOR n
  ROWS clause.
  
  You can view the access plan for a query by using a tool such
  as db2exfmt. If the access plan includes a node labelled RIDSCN
  dominating more than one branch--each of which includes a node
  labeled IXSCAN, this shows that the query plan includes
  index-oring.
  
  This APAR addresses the issue by creating a new setting for the
  DB2_REDUCED_OPTIMIZATION DB2 UDB registry variable, which will
  prevent the slow performance in this case.  It does so by
  allowing index-oring to be used in the access plan of queries
  that include the OPTIMIZE FOR n ROWS clause.
  
  To activate this setting, run
   db2set
    DB2_REDUCED_OPTIMIZATION=-1,999,999,0000000000101000000000
  (all on the same command line) and then restart DB2.
  

Problem conclusion

• DEFECT=366548 CSD>c050816 MODULE=engn_sqno
  First fixed in DB2 UDB Version 8, FixPak 11
  

Temporary fix

• Omit the OPTIMIZE FOR n ROWS clause.
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R820 PSY

     UP