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