home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
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

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 FixList