|
Problem IC77166
|
Status: Closed |
WITH STMT_CONC=LITERALS, PACKAGE CACHE PERFORMANCE CAN DEGRADE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In systems that generate large numbers of unique statements with
literals, turning statement concentrator on reduces the number
of unique SQL statements (concentrated statements) that are
compiled. However, the dynamic SQL cache can be
over-populated by statement entries of the original statement
(ie, the pre-concentrated statements that contain the literals).
These statements are themselves not compiled - but are
meant to help route repeating statements containing literals to
the same 'concentrated statement'. However due to the reduced
number of concentrated statements there is much more room for
many of the 'pre-concentrated statements'.
Depending on the package cache size and the number of unique
literal containing statements that are issued, the package cache
can contain many thousands of pre-concentrated statement
entries. This can result in the package cache containing long
chains and increasing the cost of lookup in the package cache
Using db2pd -db <dbname> -dynamic, one can evaluate the contents
of the dynamic SQL part of the package cache and determine how
the contents of the dynamic SQL cache and the amount of
statements containing literals can be monitored/evaluated. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* databases with stmt_conc=literals *
****************************************************************
* PROBLEM DESCRIPTION: *
* Package cache lookup degrades *
****************************************************************
* RECOMMENDATION: *
* upgrade to DB2 for LUW v9.7 fixpack 5 *
**************************************************************** |
| Local Fix: |
Decreasing the package cache can help reduce the impact of this
by forcing earlier eviction of the unconcentrated statements
Disabling STMT_CONC will avoid this issue - although will lose
the benefits of statement concentration |
| 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 in DB2 for LUW V9.7 fixpack 5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 26.06.2011
02.01.2012
02.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
