Analyzing Package Cache Size

Posted by

Note: updated 7/21 to reflect location of the package cache high water mark in the MON_GET* table functions

I have long been a fan of a smaller package cache size, particularly for transaction processing databases. I have seen STMM choose a very large size for the package cache, and this presents several problems:

  • Memory used for the package cache might be better used elsewhere
  • A large package cache makes statement analysis difficult
  • A large package cache may be masking statement issues – the proper use of parameter markers

Parameter Markers

Parameter markers involve telling DB2 that the same query may be executed many times with slightly different values, and that DB2 should use the same access plan, no matter what the values supplied are. This means that DB2 only has to compile the access plan once, rather than doing the same work repeatedly. However, it also means that DB2 cannot make use of distribution statistics to compute the optimal access plan. That means that parameter markers work best for queries that are executed frequently, and for which the value distribution is likely to be even or at least not drastically skewed.

The use of parameter markers is not a choice that the DBA usually gets to make. It is often a decision made by developers or even vendors. Since it is not an across-the-board best practice to use parameter markers, there are frequently cases where the wrong decisions are made. There are certainly queries and data sets where parameter markers will make things worse.

At the database level, we can use the STMT_CONC database configuration parameter (set to LITERALS) to force the use of common access plans for EVERYTHING. This is not optimal for the following reasons:

  • There are often some places where the value will always be the same, and in those places SQL would benefit more from a static value.
  • The SQL in the pacakage cache will essentially never show static values used, which can be difficult when troubleshooting.
  • With uneven distribution of data, performance of some SQL may suffer.
  • There have been APARs about incorrect data being returned.

If you have interaction with developers on a deep and meaningful level, proper use of parameter markers is the best choice.

Parameter markers show up as question marks in SQL in the package cache. This statement uses parameter markers:

Select booking_num from SAMPLE.TRAILER_BOOKING where trailer_id = ?

Statement substitutions done by the statement concentrator use :LN, where N is a number representing the position in the statement. This statement shows values affected by the statement concentrator:

select count(*) from event where event_id in ( select event_id from sample.other_table where comm_id=:L0 ) and who_entered != :L1

Sizing the Package Cache

I’ve said that I don’t trust STMM to make the best choices for the package cache. As a result, I recommend setting a static value. How do I come up with the right value?

I often start by setting the PCKCACHESZ database configuration parameter to 8192 or 16384, and tune it upwards until I stop seeing frequent package cache overflows. A package cache overflow will write messages like this to the DB2 diagnostic log:

xxxx-xx-xx-xx.xx.xx.xxxxxx+xxx xxxxxxxxxxxxxx     LEVEL: Event
PID     : xxxxxxx              TID  : xxxxx       PROC : db2sysc
0
INSTANCE: db2             NODE : 000         DB   : SAMPLE
APPHDL  : 0-xxxxx              APPID:
xx.xxx.xxx.xx.xxxxx.xxxxxxxxxxx
AUTHID  : xxxxxxxx

EDUID   : xxxxx                EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, access plan manager, sqlra_cache_mem_please,
probe:100
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             : 753
current used size (OSS)   : 15984666
maximum cache size (APM)  : 15892480
maximum logical size (OSS): 40164894
maximum used size (OSS)   : 48562176
owned size (OSS)          : 26017792
number of overflows       : xxxxx

I address these usually by increasing the package cache by 4096 until they are vastly less frequent. This could still be a considerable size if your application does not make appropriate use of parameter markers.

To look at details of your package cache size, you can look at this section of a database snapshot:

Package cache lookups                      = 16001443673
Package cache inserts                      = 4180445
Package cache overflows                    = 0
Package cache high water mark (Bytes)      = 777720137

I’m a bit frustrated that the package cache high water mark doesn’t seem to be in the MON_GET* functions. I’m going to need that before they discontinue the snapshot monitor. To get the high water mark for the package cache, you can use this query on 9.7 and above (thanks to Paul Bird’s twitter comment for pointing me to this):

select memory_pool_used_hwm
from table (MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2)) as mgmp 
where memory_pool_type='PACKAGE_CACHE' 
with ur

MEMORY_POOL_USED_HWM
--------------------
                 832

You can use that value to see how close to the configured maximum size (PCKCACHESZ) the package cache has actually come. In this particular database, the package cache size is 190000 (4K pages). In bytes that would be 778,240,000. That means in this case that the package cache has nearly reached the maximum at some point. But you can tell from the value of package cache overflows that it has not attempted to overflow the configured size.

The numbers above also allow me to calculate the package cache hit ratio. These numbers are also available in MON_GET_WORKLOAD on 9.7 and above or MON_GET_DATABASE on 10.5. The package cache hit ratio is calculated as:

100*(1-(package cache inserts/package cache lookups))

With the numbers above, that is:

100*(1-(4180445/16001443673))

or 99.97%

You do generally want to make sure your package cache hit ratio is over 90%.

In addition to these metrics, you can also look at what percentage of time your database spends on compiling SQL. This can be computed over a specific period of time using MONREPORT.DBSUMMARY. Look for this section:

  Component times
  --------------------------------------------------------------------------------
  -- Detailed breakdown of processing time --

                                      %                 Total
                                      ----------------  --------------------------
  Total processing                    100               10968

  Section execution
    TOTAL_SECTION_PROC_TIME           80                8857
      TOTAL_SECTION_SORT_PROC_TIME    17                1903
  Compile
    TOTAL_COMPILE_PROC_TIME           2                 307
    TOTAL_IMPLICIT_COMPILE_PROC_TIME  0                 0
  Transaction end processing
    TOTAL_COMMIT_PROC_TIME            0                 76
    TOTAL_ROLLBACK_PROC_TIME          0                 0
  Utilities
    TOTAL_RUNSTATS_PROC_TIME          0                 0
    TOTAL_REORGS_PROC_TIME            0                 0
    TOTAL_LOAD_PROC_TIME              0                 0

You generally want to aim for a compile time percentage of 5% or less. Remember that MONREPORT.DBSUMMARY only reports data over the interval that you give it, with a default of 10 seconds, so you want to run this over time and at many different times before making a decision based upon it.

Summary

A properly sized package cache is important to database performance. The numbers and details presented here should help you find the appropriate size for your system.

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.