Top SQL Drilldown Dynamic SQL tab
The Dynamic SQL tab on the Top SQL drilldown shows performance metrics for the dynamic SQL statements run against a monitored database or partition. The statements include those that are currently executing and those that were executed earlier in the current session. They can also include statements that were executed prior to the current session. Up to 200 statements that have been executed since a database was started can be shown. Past statements are shown even if the issuing application is not connected.
Note: Dynamic SQL statements can be issued interactively by users and built at run time. Unlike static SQL statements, they are not hard coded into applications.
From this tab, you can drill down for more details on problematic statements and tune them.
Drill down on a statement
When the Dynamic 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 dynamic statements.
The Dynamic SQL Details sub drilldown is displayed at the bottom of the Dynamic 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 Dynamic SQL Details 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 Dynamic SQL Details sub-drilldown
-
Click
on the left side of the Dynamic SQL Details 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 Dynamic SQL tab
All columns available for the Dynamic 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 |
|
Average Execution Time (microsec) |
The average execution time for a SQL statement. This value is calculated from the execution time and executions for a statement. A high average time indicates a statement should be tuned. |
|
Compilations |
The number of different compilations for a SQL statement. |
|
Data Hit Ratio |
A statement's ratio of physical reads to logical reads for data pages. |
|
Execution Time (microsec) |
The total time spent executing an SQL statement. The value is shown in microseconds. If a statement is currently executing, the value is the amount of execution time elapsed so far. |
|
Executions |
The number of times an SQL statement has been executed. This value can help you identify the most frequently executed statements in your system. |
|
Index Hit Ratio |
A statement's ratio of physical reads to logical reads for index pages. |
|
Prep Best (microsec) |
The best preparation time for an SQL statement. This is the least amount of time needed to prepare the statement. The value is shown in microseconds. By comparing best preparation to worst preparation time, you can determine whether a statement is expensive to compile. |
|
Prep Worst (microsec) |
The worst preparation time for an statement. This is the greatest amount of time needed to prepare the SQL statement. The value is shown in microseconds. By comparing worst preparation to best preparation time, you can determine whether a statement is expensive to compile. |
|
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. |
|
XML Hit Ratio |
A statement's ratio of physical reads to logical reads for XML pages. |