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 db 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://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/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://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000266.html

Related Parameters

Blog Entries Related to this Parameter

Identifying Problem SQL