|
Problem IC71343
|
Status: Closed |
NEW FEATURE ADDED IN V95 TO BETTER SERVE MOST FETCH FIRST N ROWS (FFNR) QUERIESCAUSING REGRESSION ON CERTAIN QUERY |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
New feature added in v95 to better serve most FFNR queries
causing regression on certain query. The new query plan is 2 to
3 times slower.
Query:
select productkey
from product
where lot_id = 'HE.9119.FHA.BBWP2ZJ' or lotlabel =
'HE.9119.FHA.BBWP2ZJ'
fetch first 1 rows only
with ur
In v95, optimizer generates the plan below:
Access Plan:
-----------
Total Cost: 873.854
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
2
DTQ
( 2)
873.854
863.469
|
1
TBSCAN
( 3)
2278.03
2251
|
25044
TABLE: DB2INST1
PRODUCT
Q1
In v91, the query plan:
-------------------------------
Total Cost: 52.9793
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4.18866
DTQ
( 2)
52.9793
7
|
2.09433
FETCH
( 3)
52.9593
7
/---+---\
2.09433 1.14541e+06
RIDSCN TABLE: DB2IV91
( 4) PRODUCT
37.8334
5
/-----+-----\
1.03591 1.05842
SORT SORT
( 5) ( 7)
22.6972 15.1365
3 2
| |
1.03591 1.05842
IXSCAN IXSCAN
( 6) ( 8)
22.6967 15.136
3 2
| |
1.14541e+06 1.14541e+06
INDEX: DB2IV91 INDEX: DB2IV91
NXAKPRODUCT NXPRODUCT3 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* db2 v95fp5 *
****************************************************************
* PROBLEM DESCRIPTION: *
* Certain queries running much slower after the implemented *
* the new FEATURE ADDED TO BETTER SERVE MOST FETCH FIRST N *
* ROWS (FFNR) in v95. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2 v95fp8 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Regression will not be in db2 v95fp8 onwards |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 21.09.2010
20.07.2011
20.07.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP8
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.8
|
 |
