Performance Tuning for Content Manager

Chapter 8. Tuning DB2 for Content Manager 221

Figure 8-2 Hit ratios in a spreadsheet

The Health Monitor also gives you information about this parameter. When tuning

this parameter, you should consider whether the extra memory being reserved

for the catalog cache might be more effective if it were allocated for another

purpose, such as the buffer pool or package cache.

Tuning this parameter is particularly important if a workload involves many SQL

compilations for a brief period of time, with few or no SQL compilations

thereafter. If the cache is too large, memory might be wasted holding copies of

information that will no longer be used.

In general, more cache space is required if a unit of work contains several

dynamic SQL statements, or if you are binding packages that contain a large

number of static SQL statements.

8.6.17 Package cache size (PCKCACHESZ)

Impact

DB - High

Description

This parameter is allocated out of the database shared memory, and is used for

caching of sections for static and dynamic SQL statements on a database. In a

partitioned database system, there is one package cache for each database

partition.

Caching packages enables 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 actions occurs:

 The database is shut down.

 The package or dynamic SQL statement is invalidated.

 The cache runs out of space.

222 Performance Tuning for Content Manager

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), 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.

Default values (range)

-1 (-1, 32 - 128,000) 4-KB pages for 32-bit platforms

-1 (-1, 32 - 524,288) 4-KB pages for 64-bit platforms

To update

Run the following command:

db2 update db cfg for <database name> using PCKCACHESZ <parameter value>

Our recommendation

Start with using the default.

Use the database system monitor (snapshots) to determine whether this number

should be increased. The snapshot monitor elements of importance are:

 Package cache lookups (pkg_cache_lookups): the number of times that an

application looked for a section or package in the package cache.

 Package cache inserts (pkg_cache_inserts): the total number of times that a

requested section was not available for use and had to be loaded into the

package cache.

 Package cache overflows (pkg_cache_num_overflows): the number of times

that the package cache overflowed the bounds of its allocated memory. If the

number of overflows is large, the package cache might be too small for the

workload and should be increased.

The package cache hit ratio tells you whether the package cache is being used

effectively. A ratio of more than 80% indicates that the cache is performing well.

The formula to calculate the package cache hit ratio is:

(1 - (pkg_cache_inserts / pkg_cache_lookups)) * 100

If you do consider tuning this parameter, you should think whether the extra

memory being reserved for the package cache might be more effective if it is

allocated for another purpose, such as the buffer pool or catalog cache. For this

reason, you should use benchmarking techniques when tuning this parameter.