DB2 Version 9.7 for Linux, UNIX, and Windows

Diagnosing SQL performance slowdowns using MONREPORT module reports

To effectively diagnose SQL performance slowdowns, use the Summary report to determine the problem category, and then use other reports generated by the MONREPORT module to gather additional information about the slowdown.

Before you begin

To diagnose performance problems using the MONREPORT module reports, it is helpful to compare current monitor element values to typical values. Typical values can be determined from best practices, baseline values collected previously, and current values on a comparable system. If you regularly generate reports when your system is not experiencing performance problems, then you will have a ready set of baseline data which you can compare to the reports generated during troubleshooting.

Perform any or all of the following steps.

Procedure

Review system performance monitor data to determine the problem category.
Identify the scope of the slowdown. Determine whether the slowdown affects all or most of the workload; whether it is limited to SQL statements issued from a particular connection, workload, or service class; or whether it is limited to a particular database member.
Review the top SQL statements and determine whether they are involved in the slowdown. You can also obtain execution history and other information about the SQL statements.
Review all work running on the system. Check for patterns that might determine the problem category and identify the SQL statements involved in the slowdown.
Collect statement text and details of the SQL statements involved.