ibm.com/developerWorks/ developerWorks®
DB2 problem determination using db2top utility Page 7 of 36
Lock usage (LockUsed) and escalation (LockEscals) can be very helpful to narrow down locking
issues. If a huge number of lock escalations is observed, it is a good idea to increase the
LOCKLIST and MAXLOCKS database parameters, or start looking at bad queries that may
request a huge amount of locks.
L_Reads, P_Reads, and A_Reads represent Logical Reads, Physical Reads, and Asynchronous
Reads. Combined with the hit ratio (HitRatio) value, these variables are very important to evaluate
whether most of the reads happened in memory or in disk I/O. Since disk I/O is much slower than
in-memory-access, users may prefer to access data in memory as much as possible. When users
see the HitRatio dropping low, it is then a good time to start looking at whether the bufferpools are
not large enough, or if there is any bad query requesting too much table scans and flushing out
other pages from memory to disk.
Similarly with reads, A_Writes represents Asynchronous Writes, which indicates the data pages
are written by an asynchronous page cleaner agent before the buffer pool space is required. By
knowing the number of writes happened during the elapsed time of the refresh rate of db2top,
users also know how many write requests have been made in the database. This could be useful
to calculate the average time cost per write, which may be helpful in analyzing some performance
issues caused by an I/O bottleneck. Users may expect a maximum ratio of A_Writes/Writes for
best writing I/O performance.
SortOvf represents Sort Overflow. If users find that this number goes very high, it might be good
to look around queries. Sort Overflow happens when Sortheap is not large enough, so that a
SORT or HashJoin operation may overflow the data into temp space. Sometime the value can be
dropped by increasing the size of Sortheap, but in other cases, it may not help much if the data set
being sorted is much larger than the memory that can be allocated to Sortheap. The sort overflow
could be a major bottleneck in a case like that. It may require physical I/O to proceed SORT or
Hash Join if the amount of data requested is larger than what the bufferpool can hold in temp
space. Therefore, optimizing queries to reduce the number of sort overflows could significantly
help the performance of the system.
The last four entries in the Database screen show the Average Physical Read time (AvgPRdTime),
Average Direct Read Time (AvgDRdTime), Average Physical Write time (AvgPWrTime), and
Average Direct Write time (AvgDWrTime). These four entries directly reflect the performance of
the I/O subsystem. If users observed an unexpected large amount of time spent on each Read or
Write operation, further investigation should be made into the I/O subsystem.