|
Problem IC70492
|
Status: Geschlossen |
POSSIBLE NLJOIN QUERY PERFORMANCE ISSUE FROM UNDER COSTED NLJOIN DUE TO BAD INNER PAGE ESTIMATE |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problembeschreibung: |
When examining an Query Access Plan with a Nested Loop Join,
usually the cost for the Nested Loop Join will be at least the
sum of the cost of the outer Query Access Plan and the inner
Query Access Plan. You may be experiencing a poorly performing
Query Access Plan that is caused by this APAR if the number of
I/O's associated with the Nested Loop Join is only a few I/Os
more then the number of I/Os from the outer Query Access Plan,
but the inner Query Access Plan has more then a few I/Os
indicated (typically 1 or 2 I/Os, but it could be more).
The db2exfmt plan segment below shows a Nested Loop Join
(NLJOIN) with an outer I/O of 0 I/Os, an inner I/O of 91,252
I/Os, but the Nested Loop Join I/Os of only 3. In this case the
NLJOIN I/Os should have been at least 91,252 I/Os or close to
91,252 I/Os.
715402
NLJOIN
( 23)
7367.04
3.00784 <<< I/Os too small
/-----------+----------\
1 715402
BTQ FETCH
( 24) ( 26)
0.0678342 114167
0 91252
| /-----+-----\
1 1.42166e+06 8.52996e+06
IXSCAN IXSCAN TABLE: DW
( 25) ( 27) FACT
0.0174534 19006.1 Q1
0 2376
| |
6 8.52996e+06
INDEX: DW INDEX: DW
IDX1 XIF1
Q7 Q1 |
| Problem-Zusammenfassung: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* POSSIBLE NLJOIN QUERY PERFORMANCE ISSUE FROM UNDER COSTED *
* NLJOIN DUE TO BAD INNER PAGE ESTIMATE *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to DB2 for Linux, Unix, Windows, Version 9.1 *
* Fix Pack 11 *
**************************************************************** |
| Local-Fix: |
One Possible workaround that *may* work is to get detailed stats
for the columns (frequent value & distribution stats). This will
cause the join page selectivity code to use a different
algorithm to compute the filtering. |
| verfügbare FixPacks: |
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
|
| Lösung |
The problem is corrected in DB2 for Linux, Unix, Windows,
Version 9.1 Fix Pack 11 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Bug-Verfolgung |
Vorgänger : APAR is sysrouted TO one or more of the following: IC71737 IC72461 IC72462 IC72472 IC72507 IC72523
Nachfolger : |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 11.08.2010
15.06.2011
15.06.2011 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.1.,
9.1.FP11
|
| Problem behoben lt. FixList in der Version |
| 9.1.0.10
|
 |
