|
Problem IC82599
|
Status: Closed |
HIGHER NLJOIN COSTS RESULT IN SLOWER QUERY PERFORMANCE AFTER UPGRADING TO DB2 9.7 |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
You can encounter this error only when using the database
partitioning feature.
After upgrading to DB2 9.7, you might find that some queries
with correlated subquery predicates exhibit slower performance.
Upon examining the access plans for pre-DB2 9.7 and DB2 9.7, you
might notice a difference in the tablequeue (TQ) strategy chosen
by the optimizer for the NLJOIN.
In the examples below, the DB2 9.5 access plan, prior to Fix
Pack 8, uses NLJOIN (3) with a listener broadcast TQ of the
inner, using BTQ (5), but the NLJOIN (5) for the same query in
DB2 9.7 does performs a broadcast of the outer, using BTQ (6)
and a listener directed TQ of the inner, using DTQ (8).
Furthermore, if the query includes an UPDATE operation as in the
examples below, an extra FETCH might be required, as with FETCH
(3) in the 9.7 plan.
Access Plan from DB2 9.5:
-------------------------
Total Cost: 1.37318e+06
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
149078
UPDATE
( 2)
1.37318e+06
197592
/---+----\
149078 149078
NLJOIN TABLE: DB2INST1
( 3) SAMPLE_TABLE_1
245657 Q1
48514
/---+----\
149078 1
TBSCAN BTQ*
( 4) ( 5)
25003.5 364.151
24876 48.0014
| |
149078 5
TABLE: DB2INST1 FETCH
SAMPLE_TABLE_1 ( 6)
Q4 22.7112
3.00009
/------+------\
5 1.70639e+07
IXSCAN TABLE: DB2INST1
( 7) SAMPLE_TABLE_2
15.1459 Q2
2
|
1.70639e+07
INDEX: DB2INST1
SAMPLE_INDEX_1
Q2
Access Plan from DB2 9.7:
-------------------------
Total Cost: 4.41533e+06
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
149078
UPDATE
( 2)
4.41533e+06
346683
/-----+-----\
149078 149078
FETCH TABLE: DB2INST1
( 3) SAMPLE_TABLE_1
2.49886e+06 Q1
197605
/---+----\
149078 149078
DTQ TABLE: DB2INST1
( 4) SAMPLE_TABLE_1
582180
48514
|
2.38525e+06
NLJOIN
( 5)
581155
48514
/--------+--------\
2.38525e+06 1
BTQ DTQ*
( 6) ( 8)
44939.4 38.6713
24876 3.00009
| |
149078 5
TBSCAN FETCH
( 7) ( 9)
44192 38.6017
24876 3.00009
| /------+------\
149078 5 1.70639e+07
TABLE: DB2INST1 IXSCAN TABLE: DB2INST1
SAMPLE_TABLE_1 ( 10) SAMPLE_TABLE_2
Q4 25.7429 Q2
2
|
1.70639e+07
INDEX: DB2INST1
SAMPLE_INDEX_1
Q2 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of DB2 9.7 using the database partitioning *
* feature. *
****************************************************************
* PROBLEM DESCRIPTION: *
* After upgrading to DB2 9.7, you might find that some queries *
* with correlated subquery predicates exhibit slower *
* performance. *
* Upon examining the access plans for pre-DB2 9.7 and DB2 9.7, *
* you *
* might notice a difference in the tablequeue (TQ) strategy *
* chosen *
* by the optimizer for the NLJOIN. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 7. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 |
|
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC88598
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.04.2012
25.10.2012
25.10.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.7
|
 |
