Chapter 3. Application design and system performance considerations 269
Performance considerations
A high value for this parameter decreases the frequency of checking for
deadlocks, and can cause applications to wait longer than necessary for the
resolution of a deadlock condition. A low value for this parameter increases the
frequency of checking for deadlocks and can therefore decrease run-time
performance due to increased database manager deadlock checking.
Best practices
We recommend that you let dlchktime default to 10 seconds for both OLTP and
BI environments.
Performance monitoring metrics
In UNIX, the db2dlock process performs deadlock detection. Monitor this process
for high CPU utilization using the ps aux | grep db2dlock operating system
command and increase the value of dlchktime if the CPU consumption of this
process is more than 1% or 2% in a CPU-constrained environment.
In Windows, this function is performed as a thread under the db2sysc process
and cannot be monitored.
3.4.7 Package cache considerations
The pckcachesz database configuration parameter specifies the size of the cache
used for caching sections for static and dynamic SQL statements in the
database. This is allocated out of database shared memory, and is allocated
when the database is initialized and freed when the database is shut down.
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.
Performance is enhanced when the same package or dynamic statement is used
multiple times by applications connected to the database.
Sections for static and dynamic SQL statements are kept in the package cache
until one of the following happens:
򐂰 The database is shut down.
򐂰 The static package or the dynamic SQL is invalidated.
򐂰 The package cache runs out of space.
򐂰 The package cache is reset by issuing the FLUSH PACKAGE CACHE command.
When a new package needs to be retrieved from disk and inserted into the
package cache, but there is no more space in the cache and all the packages in
the cache are in use, then overflows occur to other database memory heaps.
270 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
However, if there is no unused space in the package cache, but there are unused
packages in the package cache, then a victim is chosen from the unused
packages and its space is used for the incoming package.
The default value is -1, which corresponds to the minimum of 32 4 K pages, or 8
times the value of maxappls. This is a soft limit and can overflow to other
database shared memory heaps such as dbheap, util_heap_sz, and
catalogcache_sz. This cache can also have spill-ins from catalogcache_sz.
This parameter is configurable online.
Performance consideration
If this package cache is too small, performance can degrade due to the need for
package access from disk or the need for compiling dynamic SQL statements. In
addition, package cache overflows to other database shared memory heaps can
impact catalog cache hit ratio and result in overall system performance
degradation.
If the package cache is too big, then memory would be wasted holding copies of
the initial sections; this memory would be better served used by other database
shared memory heaps such as buffer pools or the catalog cache.
Best practices
We recommend the following best practices for tuning pckcachesz. For both
OLTP and BI environments:
򐂰 Start with the default value, and tune its value using the Database System
Monitor.
򐂰 Strive for a high package cache hit ratio of 0.8 or more, and eliminate
overflows to other heaps.
In many cases where dynamic SQL is used without parameter markers,
package cache hit ratios can be well below 0.8. In such cases, if the SQL
cannot be changed, concentrate on tuning the catalog cache.
򐂰 Choose pckcachesz equal to (Package cache high water mark / 4096) from
Figure 3-37 on page 271.
Attention: For OLTP environments where a set of SQL statements are
executed repeatedly, inserts into the package cache may be a more
significant indicator of poor performance than overflows, since they incur
constant recompiling of dynamic SQL statements that is expensive.

Get DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.