Before you analyze DB2 for performance problems, look at the overall system. If
your initial analysis suggests that the performance problem is within DB2, the
problem might be poor response time, an unexpected and unexplained high use of
resources, or locking conflicts.
Check factors such as total processor usage, disk activity, and paging.
First, turn on classes 1, 2, and 3 of the accounting trace to get a picture of task
activity and determine whether the performance problem is within DB2. Although
accounting trace class 2 has a high overhead cost for fetch-intensive applications
that do not use multi-row fetch, it is generally recommended that you keep it on in
all cases. For other applications, accounting trace class 2 has an overhead of less
than 5%. Accounting trace class 3 can help you determine which resources DB2 is
waiting on.
Next, focus on particular activities, such as specific application processes or a
specific time interval. You might see certain problems:
Slow response time
You could look at detailed traces of one slow task, a problem for which
there could be several reasons. For instance, the users could be trying to do
too much work with certain applications, work that clearly takes time, and
the system simply cannot do all the work that they want done.
Real storage constraints
Applications progress more slowly than expected because of paging
interrupts. The constraints show up as delays between successive requests
recorded in the DB2 trace.
Contention for a particular function
For example, processes might have to wait on a particular data set, or a
certain application might cause many application processes to put the same
item in their queues. Use the DB2 performance trace to distinguish most of
these cases.
Poor performance from specific individual queries or query workloads
Particular queries, and query workloads, might perform poorly because of
how the queries are written, because important statistics have not been
collected, or because useful indexes have not been created. All of these
problems can cause DB2 to make select suboptimal access paths. Use
EXPLAIN and IBM Optimization Service Center for DB2 for z/OS, to
analyze your queries and to get performance tuning recommendations.\
For information about packages or DBRMs, run accounting trace classes 7 and 8.
To determine which packages are consuming excessive resources, compare
accounting trace classes 7 and 8 to the elapsed time for the whole plan on
The easiest way to read and interpret the trace data is through the reports
produced by OMEGAMON.
阅读(1254) | 评论(0) | 转发(0) |
