Because statistics records are collected typically at 10-minute or 15-minute intervals, many records can be collected on a daily basis. Your reporting software should be able to produce summary reports, which can gather and summarize the data for a period, or detail reports that can report on every statistics interval. Start with a daily summary report, and look for specific problems within the DB2 subsystem. After you detect a problem, you can produce a detailed report to determine the specific period that the problem occurred, and also coordinate the investigation with detailed accounting reports for the same time period to attribute the problem to a specific application or process.
We recommend that you use statistics reports on a regular basis, and use monitoring software documentation, along with the DB2 Administration Guide (DB2 V8) or DB2 Performance Monitoring and Tuning Guide (DB2 9).
Note the following in a statistics report:
The statistics report should include a reason to detail the usage of the RID pool for activities such as list prefetch, multiple index access, and hybrid joins. The report will also indicate RID failures. There can be RDS failures, DM failures, and failures due to insufficient size.
If you are getting failures due to insufficient storage you can increase the RID pool size. However, if you are getting RDS or DM failures in the RID pool, the access path that is selected may be reverting to a table space scan. In these situations, determine which applications are getting these RID failures. Therefore, you have to produce a detailed statistics report that can identify the time of the failures, and also produce detailed accounting reports that show which threads are getting the failures. You to determine the packages within the plan. DB2 EXPLAIN can be used to determine which statements are using list prefetch, hybrid join, or multi-index access. You may have to test the queries to determine if they are the one's seeing the failures, and if they are, try to influence the optimizer to change the access path.
One of the most valuable piece of information in a statistics report is the section covering buffer utilization and performance. For each buffer pool in use, the report includes the size of the pool, sequential and random getpages, prefetch operations, pages that are written, and number of sequential I/Os, buffer thresholds that are reached, random I/Os, and write I/Os, and much more.
Watch for the number of synchronous reads for sequential access, which may be an indication that the number of pages is too small and pages for a sequential prefetch are stolen before they are used. Additionally watch whether any critical thresholds are reached, if there are write engines not available, and whether deferred write thresholds are triggering. It is also important to monitor the number of getpages per synchronous I/O, as well as the buffer hit ratio.
The statistics report provides important information about logging. This information includes the number of system checkpoints, number of reads from the log buffer, active log data sets, or archived log datasets, number of unavailable output buffers, and total log writes. This information could give you an indication whether you have to increase log buffer sizes or you have to investigate frequent application rollbacks or other activities that could cause excessive log reads.
The statistics report details how often database objects such as DBDs, cursor tables, and package tables are requested as well as how often those requests have to be satisfied using a disk read to one of the directory tables. You can use this information to determine if you should increase the EDM pool size. You also receive statistics about the use of dynamic statement cache and the number of times statement access paths were reused in the dynamic statement cache. This information could let you see the size of your cache and its effectiveness, but it could also highlight potential reusability of the statements in the cache.
The statistics report provides subsystem-wide perspective on the number of deadlocks and timeouts your applications have experienced. You can use this information as an overall method of detecting deadlocks and timeouts across all applications. If the statistics summary report shows a positive count, you can use the detailed report to determine what time the problems are occurring. You can also use accounting reports to determine which applications are experiencing the problem.
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|