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.
One comment