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 IC73980 Status: Geschlossen

POOR QUERY PERFORMANCE DUE TO CORRELATED TEMP ON INNER OF NLJOIN

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
The optimizer will sometimes select a Temporary Table ("TEMP" 
operator) on the inner of a Nested Loop Join (NLJOIN) in order 
to improve the performance of a query. The TEMP is intended to 
cache a complex inner's result set, to save re-computing the 
results multiple times. This fix addresses the optimizer 
undercosting the TEMP when the inner,  and the contents of the 
TEMP, can change often due to correlation that extends below the 
TEMP and into one or more joins that create that TEMP. This is 
only true for TEMPs that appear immediately on the inner of a 
NLJOIN.  This would be shown in db2exfmt output as: 
 
              NLJOIN 
             /      \ 
      operator(s)   TBSCAN(3) 
          .           | 
          .         TEMP 
          .           | 
        SCAN(1)    operators 
         Q1          . . . 
                     SCAN(2) 
                      Q2 
 
In addition, there would be one or more predicates on operator 
"SCAN(2)" that reference the table in operator "SCAN(1)". For 
example: "Q1.Column1 = Q2.Column5". Predicates of this form are 
referred to as correlated predicates. The correlated predicates 
need to appear below the TEMP, and not in "TBSCAN(3)" above the 
TEMP. Correlated predicates in "TBSCAN(3)" are common and 
expected on TEMPs on the inner of a NLJOIN that does not have 
correlation extending below the TEMP. 
 
To enable this fix, please set the following DB2 registry 
variable: 
 
 db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP 
 Recycle the DB2 instance (db2stop/db2start)
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 Linux Unix Windows v9.5 and up users with complex    * 
* correlated subqueries or Stored Procedures in their SQL.     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The optimizer will sometimes select a Temporary Table        * 
* ("TEMP" operator) on the inner of a Nested Loop Join         * 
* (NLJOIN) in order to improve the performance of a query. The * 
* TEMP is intended to cache a complex inner's result set, to   * 
* save re-computing the results multiple times. This fix       * 
* addresses the optimizer under costing the TEMP when the      * 
* inner, and the contents of the TEMP, can change often due to * 
* correlation that extends below the TEMP and into one or more * 
* joins that create that TEMP. This is only true for TEMPs     * 
* that appear immediately on the inner of a NLJOIN.  This      * 
* would be shown in db2exfmt output as:                        * 
*                                                              * 
*               NLJOIN                                         * 
*             /      \                                         * 
*       operator(s)  TBSCAN(3)                                 * 
*           .          |                                       * 
*           .        TEMP                                      * 
*           .          |                                       * 
*         SCAN(1)    operators                                 * 
*         Q1          . . .                                    * 
*                     SCAN(2)                                  * 
*                       Q2                                     * 
*                                                              * 
* In addition, there would be one or more predicates on        * 
* operator "SCAN(2)" that reference the table in operator      * 
* "SCAN(1)". For example: "Q1.Column1 = Q2.Column5".           * 
* Predicates of this form are referred to as correlated        * 
* predicates. The correlated predicates need to appear below   * 
* the TEMP, and not in "TBSCAN(3)" above the TEMP. Correlated  * 
* predicates in "TBSCAN(3)" are common and expected on TEMPs   * 
* on the inner of a NLJOIN that does not have correlation      * 
* extending below the TEMP.                                    * 
*                                                              * 
* To enable this fix, please set the following DB2 registry    * 
* variable:                                                    * 
*                                                              * 
* db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP              * 
* Recycle the DB2 instance (db2stop/db2start)                  * 
*                                                              * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5, Fixpack 8, and enable the fix by * 
* using: db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP       * 
****************************************************************
Local-Fix:
Use the following DB2 registry as a workaround: 
DB2_REDUCED_OPTIMIZATION=NO_CORR_NLJN
verfügbare FixPacks:
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

Lösung
Fixed first in DB2 for LUW Version 9.5 Fixpack 8. The 
Correlated TEMP on the inner of the Nested Loop Join will 
no longer appear in the plan.
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC75203 IC75248 IC75271 IC75303 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
19.01.2011
06.07.2011
06.07.2011
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.FP8,
9.5.FP8
Problem behoben lt. FixList in der Version
9.5.0.8 FixList