|
Problem IC72243
|
Status: Closed |
Optimizer may choose a non-optimal plan for queries using FETCH FIRST ROWS ONLY caluse, which can cause performance degradation. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Optimizer may choose a non-optimal plan for queries using FETCH
FIRST ROWS ONLY (FFNR) caluse, which can lead to performance
degradation. This plan causes a large stream of data to be
broadcasted across partitions. Users will find this evident in
the query access plan, which will show the BTQ operation
broadcasting large stream of data as following:
/--------
1.07181e+07
BTQ
( 13)
125531
31015.2
|
2.67952e+06
IXSCAN
( 14)
123504
31015.2
|
2.67952e+06
INDEX: <name of the index>
In this example, aprox 1.07x10^7 rows are being broadcasted
across partitions. This leads to poor performance. |
| Problem Summary: |
USERS AFFECTED:
===============
Customer using version 9.5 and above on all platforms.
PROBLEM DESCRIPTION:
====================
Queries using FIRST FIRST ROWS ONLY (FFNR) clause may experience
performance problem after migrating from version 8 to version 9.
The APAR can be confirmed by checking the query access graph. If
a BTQ operation is seen broadcasting large stream of data, it
may point to this APAR.
RECOMMENDATION:
===============
There is currently no workaround other than not using the FFNR
clause. |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem is first fixed in version 9.7 Fixpack 4. |
| Workaround |
There is currently no workaround other than not using the FFNR
clause. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.10.2010
03.05.2011
03.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
