What
is the package cache?
DB2
LUW uses many different memory areas to cache critical information. These
memory areas are very powerful tools in controlling database performance. The
package cache is one such area. This is where DB2 stores static and dynamic
queries, access plans, and information about the execution of those queries.
What information is in the package cache?
Just
like a gold mine, your package cache may seem like a black hole with an entire
mountain of information behind it that you must sort through to find the gold
nuggets that will make you rich.
Few of the more interesting pieces of information available are:
Number
of executions — the number of times a particular
query has been executed
Execution
time
— the total time spent on all executions of a particular query
Lock
wait time — the total time a particular query spent waiting
on locks
Sort
time
— the total time a particular query spent organizing and ordering data
Rows
read
— the total number of rows a particular query has examined
Rows
returned — the total number of rows a query has returned in
result sets
CPU
time
— the total CPU time consumed by a particular query
Execution
time
— the total time a particular query spent executing
All information in the
package cache is cumulative since the last time a particular query was placed
in the package cache.
What
information is NOT available in the package cache?
While the package cache contains a lot of useful
information, it does not replace a SQL statement event monitor.
The package
cache cannot tell you:
-When a particular query was executed, Some details
on static SQL Specific values used in execution of a statement, when that statement uses
parameter markers, Literal values used during execution .
How
long does information stay in the package cache?
DB2 moves individual statements in and out of the
package cache to keep the most frequently executed statements in memory.
Consequently, a statement might be in the package cache for a while before it
is overwritten.
You can purge the contents of the package cache :
FLUSH
PACKAGE CACHE DYNAMIC
It is important to note that the execution of this
statement can affect performance on a running system, since it will cause the
access plans for all dynamic SQL to be re-generated.
Deactivating a database will also cause the package
cache to be emptied.
