|
Problem IC69666
|
Status: Closed |
DB2 MIGHT CHOOSE A NLJOIN PLAN WITH TBSCAN IN INNER WHICH MIGHT NOT PERFORM WELL |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
DB2 optimizer might choose a NLJOIN plan with TBSCAN in the
inner.
This could occur when the optimizer estimates a very low
cardinality of the outer stream. If the actual cardinality
is higher than the one estimated by the optimizer, then the
NLJOIN might not perform
well. This fix discourages such NLJOIN plans. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* DB2 optimizer might choose a NLJOIN plan with TBSCAN in the *
* inner. Such a NLJOIN plan might not perform well. One *
* example plan is shown below ( partial ): *
* *
* ... *
* 0.846176 *
* NLJOIN *
* ( 2) *
* 42978.8 *
* 11750 *
* /---+----\ *
* 0.996981 0.848739 *
* TBSCAN TBSCAN *
* ( 3) ( 7) *
* 25.1288 42963.7 *
* 1.99753 11748 *
* | | *
* 0.996981 1.29422e+06 *
* SORT TABLE: SCHEMA1 *
* ( 4) TABLE_2 *
* 25.1285 Q2 *
* 1.99753 *
* | *
* 0.996981 *
* FETCH *
* ( 5) *
* 25.1278 *
* 1.99753 *
* /---+----\ *
* 0.996981 199388 *
* IXSCAN TABLE: SCHEMA1 *
* ( 6) TABLE_1 *
* 17.5878 Q1 *
* 1 *
* | *
* 139388 *
* INDEX: SCHEMA1 *
* IND_TABLE_1 *
* Q1 *
* ... *
****************************************************************
* RECOMMENDATION: *
* Upgrade to product Version 9.7 Fix Pack 3. *
**************************************************************** |
| Local Fix: |
The cardinality of the NLJOIN plan's outer stream can be
verified by
comparing it against actual counts. Depending upon the
situation, the cardinality can be improved in different ways
by collecting distribution statistics or using advanced
statistics such
as column group statistics or statistical views. |
| available fix packs: |
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 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The fix discourages such NLJOIN plans. The problem is first
fixed in DB2 Version 9.7 Fix Pack 3. |
| Workaround |
see LOCAL FIX |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC70174
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.07.2010
23.09.2010
23.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
