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

Subscribe

You can track all active APARs for this component.

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 number

    IC88624

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-27

  • Closed date

    2012-12-31

  • Last modified date

    2012-12-31

  • 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



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

10.1

Reference #:

IC88624

Modified date:

2012-12-31

Translate my page

Machine Translation

Content navigation