DB2 - Problem description
|Problem IC79968||Status: Closed|
INTERNAL QUERY COMPILER STRUCTURE INCORRECTLY LEFT INITIALIZED AT THE WRONG OPTIMIZATION LEVEL LEADING TO BAD PERFORMANCE
DB2 FOR LUW / DB2FORLUW / 950 - DB2
A sequence of SQL statements combined with changing of optimization level, can lead to query compilation with an incorrect query compiler setting. This can lead to poor query performance. An example of such a sequence executing on the same connection. 1) A statement is compiled using query optimization level 0 ( There is no entry for this statement in the dynamic sql (package) cache at that time. ). 2) The query optimization level special register is altered e.g. via SET CURRENT QUERY OPTIMIZATION 5 3) A new invocation occurring, for example a 'CALL PROC' statement BUT the statement that causes the invocation must itself either be static or picked up from the dynamic sql cache. 4) Inside the nested invocation, i.e. the stored procedure, it needs to drive another compile. In the stored procedure case that would include dynamic SQL or incremental bind. ( (e.g. due REOPT ONCE/ALWAYS specification or references to global temporary tables) If statements inexplicably run slower at times, then this apar may be the cause. To identify this, obtain the current execution plan using the EXPLAIN_FROM_SECTION stored procedure and compare the execution plan with an execution plan obtained through e.g. db2exfmt. A bad execution plan triggered by this apar may show a lack of certain operators that are available at a specific optimization level. ( The DB2 Infocenter contains a description on what operations are considered in different optimization classes under this section : Database fundamentals > Performance tuning > Factors affecting performance > Query optimization > Optimizing query access plans ) To remedy the problem at runtime, the statement execution needs to be cancelled and a new compilation can be forced by running either : " 'EXPLAIN PLAN FOR <the statement> " or alternatively clearing the package cache by invoking the "FLUSH PACKAGE CACHE" statement.
Suboptimal query performance due to internal compiler structure problem.
|available fix packs:|
This problem has first been fixed in V9.5 Fix Pack 9
not known / see Local fix
Date - problem reported : Date - problem closed : Date - last modified :
23.11.2011 08.03.2012 08.03.2012
|Problem solved at the following versions (IBM BugInfos)|
|Problem solved according to the fixlist(s) of the following version(s)|