DB2 - Problembeschreibung
Problem IC70977 | Status: Geschlossen |
DB2 QUERY OPTIMIZER MAY CHOOSE A NON-OPTIMAL ACCESS PLAN WHEN SKEW IS PRESENT IN THE JOIN COLUMN DATA | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
This only applies to a DPF environment. For queries involving joins of non-collocated tables, the query optimizer may favour repartitioning one stream over broadcasting the other to perform the join. If the join column that the stream is partitioned on is highly skewed, the broadcast alternative may be a better choice. This apar will improve the optimizer's model of skew in a DPF environment. This is more likely to impact systems with hundreds of database partitions. You may be experiencing this issue if one database partition is performing a significant larger amount of work than other partitions. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * Problem is specific to DPF environment * **************************************************************** * PROBLEM DESCRIPTION: * * For queries involving joins of non-collocated tables, * * thequery optimizermight favour repartitioning one stream * * over broadcasting theother toperform the join. If the join * * column that the stream ispartitioned onis highly skewed, the * * broadcast alternative may be a betterchoice. This apar will * * improve the optimizer's model of skewina DPF environment. * * This is more likely to impact systemswithhundreds of * * database partitions.You may be experiencing this issue if * * one database partitionisperforming a significant larger * * amount of work than otherpartitions. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 Fix Pack 4 and set * * the DB2_EXTENDED_OPTIMIZATION registry variable to * * db2set DB2_EXTENDED_OPTIMIZATION=DPFCOST_SKEW * * Note: if you already have DB2_EXTENDED_OPTIMIZATION * * set, then you will need to include it when setting this new * * keyword; for example, * * DB2_EXTENDED_OPTIMIZATION=YES,DPFCOST_SKEW * **************************************************************** | |
Local-Fix: | |
1. The optimizer relies on non-uniform distribution statistics to model the skew. If distribution statistics are not collected on the join column(s), the first step required in resolving this issue is to ensure distribution statistics are collected. 2. If distribution statistics are collected and the problem persists, replicated tables can be created to eliminate the need to repartition the data stream. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in Version 9.7 Fix Pack 4 | |
Workaround | |
See LOCAL_FIX | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 10.09.2010 19.05.2011 19.05.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP4 | |
Problem behoben lt. FixList in der Version | |
9.7.0.4 |