db2top 监控命令摘要

http://www.ibm.com/developerworks/data/library/techarticle/dm-0812wang/

下面是对上面博文的部分摘要

What can be monitored by db2top?

Database (d)

Users can monitor active session (MaxActSess), sort memory (SortMemory), and log space (LogUsed). These monitoring elements can help users identify what is the current percentage of usage for those elements. If one of those elements starts reaching high or even 100 percent, users should start to investigate what happened.

The elapsed time between database Start Time and the current time can be used to understand how long the database has being activated. This value can be very useful when combined with other monitoring elements to investigate issues that have been floating around over a period of time.

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.