DB2 - Problembeschreibung
Problem IC88624 | Status: Geschlossen |
PERFORMANCE DEGRADATION WHEN PACKAGE CACHE IS FULL AND MANY STATEMENTS ARE IN THE CACHE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * 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. | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows | |
Lösung | |
This problem was first fixed in DB2 V10.1 FixPack 2. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 27.11.2012 31.12.2012 31.12.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.2 | |
10.5.0.2 |