Package Cache Size (pckcachesz)

Configuration Type

Database

Parameter Type

Configurable

Default [Range]

-1 [ -1, 32 - 64 000 ]

Unit of Measure

Pages (4KB)

When Allocated

When the database is initialized

When Freed

When the database is shutdown

This parameter is allocated out of the database global memory, and is used for caching static and dynamic SQL statements on a database. In a partitioned databasesystem, there is one package cache for each database partition.

Caching packages allows the database manager to reduce its internal overhead by eliminating the need to access the system catalogs when reloading a package; or, in the case of dynamic SQL, eliminating the need for compilation. Sections are kept in the package cache until one of the following occurs:

• The database is shut down
• The package or dynamic SQL statement is invalidated
• The cache runs out of space.

This caching of the section for a static or dynamic SQL 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 application.

By taking the default (-1) in a server or partitioned database environment, 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 bufferpool. 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.

See the System Monitor Guide and Reference for information about the following monitor elements:

• pkg_cache_lookups (package cache lookups)
• pkg_cache_inserts (package cache inserts)
• pkg_cache_size_top (largest package cache size)
• pkg_cache_num_overflows (number of package cache overflows)

These database system monitor elements can help you determine whether you should adjust this configuration parameter.

Note:

The package cache is a working cache, so you cannot set this parameter to zero. There must be sufficient memory allocated in this cache to hold all sections of the SQL statements currently being executed. If there is more space allocated than currently needed, then sections are cached. These sections can simply be executed the next time they are needed without having to load or compile them. The limit specified by the pckcachesz parameter is a soft limit. This limit may 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.