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

QUERY PERFORMANCE CAN BE POOR WHEN JOINING TO A DATA PARTITIONED TABLE
USING THE DATA PARTITIONING COLUMNS.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
The DB2 Optimizer may underestimate the number of pages read 
from disk when a join to a data partitioned table is using the 
data partitioning columns. This can cause the optimizer to 
choose a plan that will perform poorly. 
 
This may be applicable to queries with an inner join involving a 
data partitioned table or an outer join when the NULL producing 
input to the join is a data partitioned table. In both cases, 
the query will have equality (=) join predicates involving all 
the data partitioning columns of the data partitioned table. 
 
For the poorly performing query, obtain the output from db2exfmt 
and look for a NLJOIN operator with a TBSCAN of the data 
partitioned table as the right-side input. Furthermore the 
estimated I/O cost of the NLJOIN will look small in comparison 
to the size of the data partitioned table. 
 
For example: 
            4.52747e+07 
              NLJOIN 
              (  27) 
              7.66338 
               1985                  <<< I/O Cost estimate is 
small given the large table 
        /-------+--------\ 
                         37666.1 
                         TBSCAN 
                         (  29) 
                         7.64325 
                          1985 
                           | 
                       4.52747e+07 
                   DP-TABLE: DB2INST1 
                       PARTTABLE1 
                           Q11 
 
When examining the operator details for the TBSCAN(29), the join 
predicates are used a DP Elim predicates: 
 
        DP Elim Predicates: 
        ----------------- 
        Range 1) 
            Start Predicate: (Q11.PARTCOL = Q10.COL1 
            Stop  Predicate: (Q11.PARTCOL = Q10.COL1
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of range partitioned tables.                           * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* he DB2 Optimizer may underestimate the number of pages read  * 
* from disk when a join to a data partitioned table is using   * 
* the data partitioning columns. This can cause the optimizer  * 
* to choose a plan that will perform poorly.                   * 
*                                                              * 
* This may be applicable to queries with an inner join         * 
* involving a data partitioned table or an outer join when the * 
* NULL producing input to the join is a data partitioned       * 
* table. In both cases, the query will have equality (=) join  * 
* predicates involving all the data partitioning columns of    * 
* the data partitioned table.                                  * 
*                                                              * 
* For the poorly performing query, obtain the output from      * 
* db2exfmt and look for a NLJOIN operator with a TBSCAN of the * 
* data partitioned table as the right-side input. Furthermore  * 
* the estimated I/O cost of the NLJOIN will look small in      * 
* comparison to the size of the data partitioned table.        * 
*                                                              * 
* For example:                                                 * 
* 4.52747e+07                                                  * 
* NLJOIN                                                       * 
* (  27)                                                       * 
* 7.66338                                                      * 
* 1985                  <<< I/O Cost estimate is               * 
* small given the large table                                  * 
* /-------+--------\                                           * 
* 37666.1                                                      * 
* TBSCAN                                                       * 
* (  29)                                                       * 
* 7.64325                                                      * 
* 1985                                                         * 
* |                                                            * 
* 4.52747e+07                                                  * 
* DP-TABLE: DB2INST1                                           * 
* PARTTABLE1                                                   * 
* Q11                                                          * 
*                                                              * 
* When examining the operator details for the TBSCAN(29), the  * 
* join predicates are used a DP Elim predicates:               * 
*                                                              * 
* DP Elim Predicates:                                          * 
* -----------------                                            * 
* Range 1)                                                     * 
* Start Predicate: (Q11.PARTCOL = Q10.COL1                     * 
* Stop  Predicate: (Q11.PARTCOL = Q10.COL1                     * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 Fix Pack 2 or later release           * 
* containing the fix.                                          * 
****************************************************************
Local-Fix:
1) Create an optimization profile for the query forcing either a 
different join method for the join in question or forcing the 
access of the data partitioned table to use an IXSCAN of a good 
index. 
 
2) Increase the values for SYSSTAT.TABLES.NPAGES and 
SYSSTAT.TABLES.FPAGES for the data partitioned table until a 
different plan is chosen.
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
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 9a 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 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in Version 9.7 Fix Pack 2.
Workaround
See Local Fix.
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC67756 IC67983 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
03.03.2010
19.07.2010
19.07.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP2
Problem behoben lt. FixList in der Version
9.7.0.2 FixList