On December 12th, I’ll be presenting on the DB2 Night Show on HADR and TSAMP Advanced Topics. Sign up here: http://www.dbisoftware.com/db2nightshow/

Parameter Wednesday – DB CFG – pckcachesz

DB2 Version This Was Written For

9.7

Parameter Name

PCKCACHESZ

Where This Parameter Lives

Database Configuration

How To Check Value

> db2 get db cfg for sample |grep PCKCACHESZ
 Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(250509)

OR

> db2 "select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBCFG where name='pckcachesz' with ur"

NAME                             VALUE            VALUE_FLAGS DEFERRED_VALUE   DEFERRED_VALUE_FLAGS DATATYPE
-------------------------------- ---------------- ----------- ---------------- -------------------- ----------------
pckcachesz                       250509           AUTOMATIC   250509           AUTOMATIC            BIGINT

Description

Specifies size (in 4KB pages) of the area of memory used for caching static and dynamic SQL statements and information about those statements that is summarized across executions.

Impact

This is a paramter with a high possibility for performance impact. Also in setting it too high, you may get unusually large dynamic sql snapshots or output from MON_GET_PKG_CACHE_STMT

Default

AUTOMATIC

Range/Values

32 - 2,147,483,646

Recycle Required To Take Effect?

No – changes to this parameter take effect immediately if there is space in database shared memory.

Can It Be Set To AUTOMATIC?

Yes, this can be set to AUTOMATIC, however it is one that I’m most likely not to set at AUTOMATIC.

How To Change It

 db2 update dbm cfg for dbname using PCKCACHESZ 120

Rule of Thumb

If you really have no clue, Automatic will work as long as you’re not storing snapshot information on disk – if you are, then you may need to consider setting this to something else. If you want to use a hard value, 8192 isn’t a bad place to start.

Tuning Considerations

This is a parameter that assuming you’re not going with AUTOMATIC, you really do need to observe over time and set based on the activity in your environment. This actually falls into my top 10 physical performance tuning areas. When manually tuning this, you want to look at the following:

> db2 get snapshot for database on sample |grep "Package cache"
Package cache lookups                      = 2326089
Package cache inserts                      = 32733
Package cache overflows                    = 0
Package cache high water mark (Bytes)      = 688635351

And calculate:
1-Package cache inserts/Package cache lookups

So in this case, that would be:

1-32733/2326089=0.985925=98.5%

That’s also called your package cache hit ratio. To calculate it in one step, based on the mon_get functions, use:

> db2 "select decimal(PKG_CACHE_INSERTS,10,3) as PKG_CACHE_INSERTS, decimal(PKG_CACHE_LOOKUPS,10,3) as PKG_CACHE_LOOKUPS, decimal(1-decimal(PKG_CACHE_INSERTS,10,3)/decimal(PKG_CACHE_LOOKUPS,10,3),10,3) as pkg_cache_hit_ratio from table(SYSPROC.MON_GET_WORKLOAD('SYSDEFAULTUSERWORKLOAD', -2)) as t with ur"

PKG_CACHE_INSERTS PKG_CACHE_LOOKUPS PKG_CACHE_HIT_RATIO
----------------- ----------------- -------------------
        32750.000       2329102.000               0.985

Obviously if you’re using WLM, you might have to tweak the above, but if you’re not (and it’s a pay-for-use feature), then the above should encompass essentially all database activity.

As with many hit ratios, we’d love to see this above 95%, and can usually be happy with it above 90% and may accept it as low as 80%

Related Error Messages

 

War Stories From The Real World

Man, am I the only one who can never spell this one right? Seriously, “pck”? I always go for “pkg”, plus can never remember whether there’s an underscore before the “sz”.

On a more serious note, this is one I’m likely to tune. On many of our new installations, I’ve been going with the default of AUTOMATIC, but have been questioning that lately and playing with it. The reason? Often DB2 makes this so big that I gather so much information in my hourly dynamic sql snapshots that I fill up 5GB in less than two days. And the impact between this rather large size and and a much more reasonable one seems to be 1% or less in the package cache hit ratio. I’m probably going to start setting this one to a hard value on new installations going forward – I just don’t see enough benefit from the gigundo size STMM seems to be fond of.

This is one of the parameters for which the setting is a soft limit, so db2 can use more than the number you specify (instead of giving you an error). If you’re concerned this may be occuring, you can look at “Package cache high water mark” in a database snapshot.

I’ve always found the package cache to be just fine for following SQL – mostly because I work with e-commerce databases where most of the SQL is canned, and my problem SQL always involves multiple executions – so is likely to stay in the package cache. If this is not true for you, you may want to consider using the new event monitor for the package cache (http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.mon.doc/doc/c0056443.html). It apparently catches information as it leaves the package cache.

Link To Info Center

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0000266.html

Related Parameters

Blog Entries Related to this Parameter

Identifying Problem SQL

1 comment… add one

  • My package cache hit ratio is coming out fine to 99 % . It is not set to automatic . Package cache size (4KB) (PCKCACHESZ) = 15000

    db2 get snapshot for database on xxxx| grep “Package cache”
    Package cache lookups = 63917060
    Package cache inserts = 159813
    Package cache overflows = 132
    Package cache high water mark (Bytes) = 613886467

    But still in db2diag.log it is giveing overflow error as below. Overflows 132 times. Could this lead the performace issue.. or Is there any issues.

    MESSAGE : ADM4500W A package cache overflow condition has occurred. There is
    no error but this indicates that the package cache has exceeded the
    configured maximum size. If this condition persists, you should
    perform additional monitoring to determine if you need to change the
    PCKCACHESZ DB configuration parameter. You could also set it to
    AUTOMATIC.
    REPORT : APM : Package Cache : info
    IMPACT : Unlikely
    DATA #1 : String, 274 bytes
    Package Cache Overflow
    memory needed : 817
    current used size (OSS) : 61403058
    maximum cache size (APM) : 59596800
    maximum logical size (OSS): 613885331
    maximum used size (OSS) : 629473280
    owned size (OSS) : 75300864
    number of overflows : 132

    Please help thanks and Regards
    Sachin

    Reply

Leave a Comment