IC88624: PERFORMANCE DEGRADATION WHEN PACKAGE CACHE IS FULL AND MANY STATEMENTS ARE IN THE CACHE

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error 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.
  

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.
  

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.                  *
  ****************************************************************
  

Problem conclusion

• This problem was first fixed in DB2 V10.1 FixPack 2.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IC82616

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP