pckcachesz - Package cache size configuration parameter
This parameter is allocated out of the database shared memory, and is used for caching of sections for static and dynamic SQL and XQuery statements on a database.
- Configuration type
- Parameter type
- Configurable online
- Configurable by member in a Db2® pureScale® environment
- Propagation class
- Default [range]
- 32-bit operating systems
- Automatic [-1, 32 - 128 000]
- 64-bit operating systems
- Automatic [-1, 32 - 2 147 483 646]
- Note: The default value is subject to change by the Db2 Configuration Advisor after initial database creation.
- Unit of measure
- Pages (4 KB)
- When allocated
- When the database is initialized
- When freed
- When the database is shut down
In a partitioned database system, there is one package cache for each database partition.
- The database is shut down
- The package or dynamic SQL or XQuery statement is invalidated
- The cache runs out of space.
This caching of the section for a static or dynamic SQL or XQuery statement can improve performance, especially when the same statement is used multiple times by applications connected to a database. This is particularly important in a transaction processing environment.
When this parameter is set to AUTOMATIC, it is enabled for self tuning. When self_tuning_mem is set to ON, the memory tuner will dynamically size the memory area controlled by pckcachesz as the workload requirements change. Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self tuning in order for self tuning to be active.
Automatic tuning of this configuration parameter will only occur when self tuning memory is enabled for the database (the self_tuning_mem configuration parameter is set to ON.)
When this parameter is set to -1, the value used to calculate the page allocation is eight times the value specified for the maxappls configuration parameter. The exception to this occurs if eight times maxappls is less than 32. In this situation, the default value of -1 will set pckcachesz to 32.
Recommendation: When tuning this parameter, you should consider whether the extra memory being reserved for the package cache might be more effective if it was allocated for another purpose, such as the buffer pool or catalog cache. For this reason, you should use benchmarking techniques when tuning this parameter.
Tuning this parameter is particularly important when several sections are used initially and then only a few are run repeatedly. If the cache is too large, memory is wasted holding copies of the initial sections.
- pkg_cache_lookups (package cache lookups)
- pkg_cache_inserts (package cache inserts)
- pkg_cache_size_top (package cache high water mark)
- pkg_cache_num_overflows (package cache overflows)
The limit specified by the pckcachesz parameter is a soft limit. This limit can be exceeded, if required, if memory is still available in the database shared set. You can use the pkg_cache_size_top monitor element to determine the largest that the package cache has grown, and the pkg_cache_num_overflows monitor element to determine how many times the limit specified by the pckcachesz parameter has been exceeded.