|
Problem IC75203
|
Status: Closed |
POOR QUERY PERFORMANCE DUE TO CORRELATED TEMP ON INNER OF NLJOIN |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The optimizer will sometimes select a Temporary Table ("TEMP"
operator) on the inner of a Nested Loop Join (NLJOIN) in order
to improve the performance of a query. The TEMP is intended to
cache a complex inner's result set, to save re-computing the
results multiple times. This fix addresses the optimizer
undercosting the TEMP when the inner, and the contents of the
TEMP, can change often due to correlation that extends below the
TEMP and into one or more joins that create that TEMP. This is
only true for TEMPs that appear immediately on the inner of a
NLJOIN. This would be shown in db2exfmt output as:
NLJOIN
/ \
operator(s) TBSCAN(3)
. |
. TEMP
. |
SCAN(1) operators
Q1 . . .
SCAN(2)
Q2
In addition, there would be one or more predicates on operator
"SCAN(2)" that reference the table in operator "SCAN(1)". For
example: "Q1.Column1 = Q2.Column5". Predicates of this form are
referred to as correlated predicates. The correlated predicates
need to appear below the TEMP, and not in "TBSCAN(3)" above the
TEMP. Correlated predicates in "TBSCAN(3)" are common and
expected on TEMPs on the inner of a NLJOIN that does not have
correlation extending below the TEMP.
To enable this fix, please set the following DB2 registry
variable:
db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP
Recycle the DB2 instance (db2stop/db2start) |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All DB2 Linux Unix Windows v9.7 and up users with complex *
* correlated subqueries or Stored Procedures in their SQL. *
****************************************************************
* PROBLEM DESCRIPTION: *
* The optimizer will sometimes select a Temporary Table *
* ("TEMP" operator) on the inner of a Nested Loop Join *
* (NLJOIN) in order to improve the performance of a query. The *
* TEMP is intended to cache a complex inner's result set, to *
* save re-computing the results multiple times. This fix *
* addresses the optimizer under costing the TEMP when the *
* inner, and the contents of the TEMP, can change often due to *
* correlation that extends below the TEMP and into one or more *
* joins that create that TEMP. This is only true for TEMPs *
* that appear immediately on the inner of a NLJOIN. This *
* would be shown in db2exfmt output as: *
* *
* NLJOIN *
* / \ *
* operator(s) TBSCAN(3) *
* . | *
* . TEMP *
* . | *
* SCAN(1) operators *
* Q1 . . . *
* SCAN(2) *
* Q2 *
* *
* In addition, there would be one or more predicates on *
* operator "SCAN(2)" that reference the table in operator *
* "SCAN(1)". For example: "Q1.Column1 = Q2.Column5". *
* Predicates of this form are referred to as correlated *
* predicates. The correlated predicates need to appear below *
* the TEMP, and not in "TBSCAN(3)" above the TEMP. Correlated *
* predicates in "TBSCAN(3)" are common and expected on TEMPs *
* on the inner of a NLJOIN that does not have correlation *
* extending below the TEMP. *
* *
* To enable this fix, please set the following DB2 registry *
* variable: *
* *
* db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP *
* Recycle the DB2 instance (db2stop/db2start) *
* *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7, Fixpack 5, and enable the fix by *
* using: db2set DB2_EXTENDED_OPTIMIZATION=NLJN_CORR_TEMP *
**************************************************************** |
| Local Fix: |
Use the following DB2 registry as a workaround:
DB2_REDUCED_OPTIMIZATION=NO_CORR_NLJN |
| available fix packs: |
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 |
Fixed first in DB2 for LUW Version 9.7 Fixpack 5. The
Correlated TEMP on the inner of the Nested Loop Join will
no longer appear in the plan. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 23.03.2011
03.01.2012
03.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5,
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
