DB2 - Problembeschreibung
Problem IC75151 | Status: Geschlossen |
POSSIBLE NLJN QUERY PERFORMANCE ISSUE FROM UNDER COSTED NLJN DUE TO BAD INNER PAGE ESTIMATE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - 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 NLJN QUERY PERFORMANCE ISSUE FROM UNDER COSTED NLJN * * DUE TO BAD INNER PAGE ESTIMATE * **************************************************************** * RECOMMENDATION: * * Please upgrade to Version 9.7 Fixpack 4 or later * **************************************************************** | |
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.7 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Problem first fixed in Version 9.7 Fixpack 4 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 21.03.2011 05.05.2011 05.05.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP4 | |
Problem behoben lt. FixList in der Version | |
9.7.0.4 |