Spotlight on DB2 6.9.3 - Getting Started Guide

Top SQL drilldown

The Top SQL drilldown shows performance metrics for the SQL statements executed in an active database or partition. It is designed to reveal statements that are the "top SQL" in terms of being the most resource intensive and high cost. For example, you can use it to identify statements that consume the most CPU, statements with the lowest hit ratio, or statements with the most sort overflows.

Metrics are shown in tabs for current, dynamic, and static SQL statements. From each top-level tab, you can drill down to detailed information on problematic statements. This is provided in sub drilldowns, which are displayed in the lower half of the window when a statement is selected. The additional information can help you diagnose why a statement is performing poorly.

Using the Top SQL drilldown

The steps below outline the basic workflow involved in using the Top SQL drilldown.

To use the top SQL drilldown

1. View top-level tabs to detect poorly performing SQL statements problems in client applications. The tabs and the SQL they show are as follows:

   • Current SQL tab—This tab shows metrics for statements that are currently executing in a database or partition. The statements are those issued by applications that currently connected to the database or partition.
   • Dynamic SQL tab—This tab shows metrics for dynamic SQL statements that are currently executing or have executed in the past for a database or partition. Past statements are those that have been executed since been executed since a database was started. The statements are those issued by applications that are currently connected or that have been connected at some point since the database was started.
   • Static SQL tab—This tab shows metrics for static SQL statements that are currently executing or have been executed in the past for a database or partition. Past statements are those that have been executed since the current session was started. The statements are those issued by applications that currently connected to the database or partition.

   Tip: You can use various display features to focus on the most problematic statements. You can organize columns from left to right, hide or show columns, and sort the tab by values in a certain column. To sort by a certain column, click the column header. Being able to sequence and sort columns in different ways allows you to quickly identify different performance problems.

2. When you detect a problematic statement in a top-level tab, view its sub drilldown tab to see additional information and view the statement. Click the row for the statement to display the sub drilldown. The additional information can help you diagnose why a statement is performing poorly. (To close the sub drilldown, click the X button in the top right corner of the drilldown.)
3. After you detect and diagnose problematic statements, you can tune them using SQL Optimizer for DB2. (You must have either product licensed and installed on the on the same computer where the Spotlight for DB2 client resides.) SQL tuning sessions can be opened from the sub drilldown for each tab.

Related topics

Tuning an SQL statement

Printing the Top SQL drilldown