Top SQL Drilldown Current SQL tab
The Current SQL tab on the Top SQL drilldown shows metrics for SQL statements that are currently executing against a monitored database or partition. The statements can be either dynamic or static.
Note: Spotlight captures information for statements that are in flight when it is refreshing. If a statement is not in flight during a refresh, information is not captured. This means a statement might not be shown or that values might not reflect all executions of a statement.
From this tab, you can drill down for more details on problematic statements and tune them.
Drill down on a statement
When the Current SQL tab shows a problematic statement, you can drill down to view additional performance information specific to that statement.
To drill down on a specific statement
Click the statement in the list of currently executing statements.
The Unit of Work sub drilldown is displayed at the bottom of the Current SQL tab. This sub drilldown shows the selected statement and provides additional metrics for this statement. Use these metrics to diagnose why the statement is performing poorly. Then, from within this sub drilldown, you can open a tuning session on the statement.
Tune a statement
From the Unit of Work sub drilldown, you can open a tuning session on the currently displayed statement if you have either of the following products licensed and installed on the same computer where your Spotlight on IBM® DB2® LUW client is installed:
-
Spotlight on IBM DB2 LUW
To tune the SQL statement currently displayed in the Unit of Work sub-drilldown
-
Click
on the left side of the Unit of Work sub drilldown.
SQL Optimizer for DB2 opens, showing the SQL statement that you were examining in Spotlight in the product's main window.
-
If you need instructions to proceed with the tuning session, click F1 for context-sensitive help.
Columns in the Current SQL tab
All columns available for the Current SQL tab are listed below in alphabetic order. Initially, the tab contains Spotlight's default column selection. You can choose which columns to display and organize them in any order from left to right using the Organize Columns function. This can be selected from the right-click menu for column headers.
|
Column |
Description |
|
Agent ID |
The application handle of the agent for the application that issued an SQL statement. |
|
Database |
The name of the database on which an SQL statement is executing. |
|
Data Hit Ratio |
A statement's ratio of physical reads to logical reads for data pages. |
|
Elapsed Time (microsec) |
The amount of execution time elapsed so far for a statement in microseconds. This changes when the interface is refreshed. |
|
Index Hit Ratio |
A statement's ratio of physical reads to logical reads for index pages. |
|
Operation |
The operation currently being processed for an SQL statement. One of the following can be shown:
|
|
Temp Data Hit Ratio |
A statement's ratio of physical reads to logical reads for data pages read from temporary tablespaces. |
|
Temp Index Hit Ratio |
A statement's ratio of physical reads to logical reads for index pages read from temporary tablespaces. |
|
Temp XML Hit Ratio |
A statement's ratio of physical reads to logical reads for XML pages read from temporary tablespaces. |
|
Text |
The text of an SQL statement. For dynamic SQL statements, this is the SQL text associated with a package. |
|
Type |
The type for an SQL statement. Either DYNAMIC or STATIC is shown. |
|
XML Hit Ratio |
A statement's ratio of physical reads to logical reads for XML pages. |