|
Problem IC66166
|
Status: Closed |
DB2 QUERY OPTIMIZER MAY CHOOSE A NON-OPTIMAL ACCESS PLAN WHEN SKEW IS PRESENT IN THE JOIN COLUMN DATA |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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.5 Fix Pack 7 and set *
* theDB2_EXTENDED_OPTIMIZATION registry variable todb2set *
* DB2_EXTENDED_OPTIMIZATION=DPFCOST_SKEWNote: if you already *
* have DB2_EXTENDED_OPTIMIZATION set,then you will need to *
* include it when setting this newkeyword; for example,db2set *
* 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. |
| available fix packs: |
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
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
|
| Solution |
Problem was first fixed in Version 9.5 Fix Pack 7 |
| Workaround |
See LOCAL_FIX |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC70977 IC71755
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.02.2010
15.11.2010
15.11.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.7
|
 |
| 9.5.0.7
|
|
