|
Problem IC88624
|
Status: Closed |
PERFORMANCE DEGRADATION WHEN PACKAGE CACHE IS FULL AND MANY STATEMENTS ARE IN THE CACHE |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Parformance degradation on running dynamic SQL can be observed
if all the following conditions are met.
- Package cache become full, i.e package cache logical size
reaches or exceeds PCKCACHESZ.
- Dynamic cache is filled by statement entries that cannot be
removed from
the cache since they are associated with an application
statement handle
that has been prepared.
In this condition, all subsequent dynamic SQL requests would
perform cache cleanup effort so that new cache entries to be
inserted into package cache. But when all or most of the entries
in the cache cannot be removed due to statement entries that
cannot be removed, it will be a wasted effort, and it leads to
latch
contention in sqlra_csm_del_stmt_empties like as below:
getConflict
sqlra_csm_del_stmt_empties
sqlra_cache_space_mgmt
sqlra_cache_reserve_memory
Users can also see high CPU symptom on the DB2 instance in this
situation.
Issuing db2pd -db <dbname> -dynamic, a high ratio of "Dynamic
SQL Statement" entries to "Dynamic SQL Variations" will make it
more likely to be exposed to this issue.
As per the Local Fix, this is likely indicative of a poorly
tuned package cache and the package cache size should be
increased. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users who run many kinds of dynamic SQL statements. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to V10.1 FixPack 2 or later. *
**************************************************************** |
| Local Fix: |
Please apply one or both of the followings.
a) Increase PCKCACHESZ so that package cache satisfies to store
cache entries for prepared statement cache.
b) Decrease the number of prepared statement cache. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
This problem was first fixed in DB2 V10.1 FixPack 2. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.11.2012
31.12.2012
31.12.2012 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.2
|
 |
| 10.5.0.2
|
|
