|
Problem IC69666
|
Status: Geschlossen |
DB2 MIGHT CHOOSE A NLJOIN PLAN WITH TBSCAN IN INNER WHICH MIGHT NOT PERFORM WELL |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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. |
| verfügbare FixPacks: |
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
|
| Lösung |
The fix discourages such NLJOIN plans. The problem is first
fixed in DB2 Version 9.7 Fix Pack 3. |
| Workaround |
see LOCAL FIX |
| Bug-Verfolgung |
Vorgänger : APAR is sysrouted TO one or more of the following: IC70174
Nachfolger : |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 02.07.2010
23.09.2010
23.09.2010 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FP3
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
