|
Problem IC66854
|
Status: Closed |
QUERY PERFORMANCE CAN BE POOR WHEN JOINING TO A DATA PARTITIONED TABLE USING THE DATA PARTITIONING COLUMNS. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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. |
| available fix packs: |
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
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 2. |
| Workaround |
See Local Fix. |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC67756 IC67983
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.03.2010
19.07.2010
19.07.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
