|
Problem IC72250
|
Status: Closed |
TUNING OF STATEMENTS FROM OPM EXTENDED INSIGHT ANALYSIS DASHBOARD DOES NOT USE THE ACCESS PLAN FROM THE LAST EXECUTION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Starting with OQT (Optim Query Tuner) 2.2 FP2, when connected to
a DB2 V97 server, OPM (Optim Performance Manager) users can tune
a SQL statement based on the access plan from the package cache,
which is the access plan of the last execution. This is done
via the OPM Extended Insight Analysis Dashboard.
A problem exists where tuning is not based on the access plan in
the package cache, but is instead based on a new access plan
obtained from submitting the query to the DB2 query compiler.
This new access plan may, or may not, be the access plan of the
last execution.
This problem can be detected by examining the details of the
RETURN node of the Access Plan Graph obtained when Collect
Actual Execution Values is not selected:
- when the problem is not present and tuning is based on the
access plan in the package cache, the RETURN node will show
EXECUTID in the Attributes section
- when the problem is present and tuning is not based on the
access plan in the package cache, the RETURN node will not show
EXECUTID in the Attributes section
The problem was first introduced in DB2 Version 9.7 Fix Pack 3. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Starting with OQT (Optim Query Tuner) 2.2 FP2, when *
* connected to *
* a DB2 V97 server, OPM (Optim Performance Manager) users can *
* tune *
* a SQL statement based on the access plan from the package *
* cache, *
* which is the access plan of the last execution. This is *
* done *
* via the OPM Extended Insight Analysis Dashboard. *
* *
* *
* *
* A problem exists where tuning is not based on the access *
* plan in *
* the package cache, but is instead based on a new access plan *
* *
* obtained from submitting the query to the DB2 query *
* compiler. *
* This new access plan may, or may not, be the access plan of *
* the last execution. *
* *
* *
* *
* This problem can be detected by examining the details of the *
* *
* RETURN node of the Access Plan Graph obtained when Collect *
* *
* Actual Execution Values is not selected: *
* *
* - when the problem is not present and tuning is based on the *
* *
* access plan in the package cache, the RETURN node will show *
* *
* EXECUTID in the Attributes section *
* *
* - when the problem is present and tuning is not based on the *
* *
* access plan in the package cache, the RETURN node will not *
* show *
* EXECUTID in the Attributes section *
* *
* *
* *
* The problem was first introduced in DB2 Version 9.7 Fix Pack *
* 3. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 4. *
**************************************************************** |
| Local Fix: |
No local fix is available to tune the query based on the access
plan of the last execution in the package cache. A work around
is to tune from the access plan obtained from re-submitting the
query to the DB2 query compiler. |
| available fix packs: |
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 problem is first fixed in DB2 Version 9.7 Fix Pack 4. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.10.2010
28.04.2011
28.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
