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. |
Static SQL tab
The Static SQL tab shows performance metrics for the static SQL statements run against a monitored database or partition. The statements include those that are executing and those that were executed earlier in the current session.
Note: Static SQL statements are hard-coded in an application and do not change each time the application is run.
From this tab, you can drill down for more details on problematic statements and tune them.
How static SQL is captured
Information in the Static SQL tab is collected by the SQL Archiver. This is a Spotlight utility that captures and accumulates statistics for static SQL statements over time. Information is captured for statements that are in flight during a Spotlight refresh. Because only in-flight statements are captured, the information represents a sampling of static SQL statistics. It does not include information about every static SQL statement run against a database or all information about a certain statement. This means that a statement might not be shown in the Static tab or that the values shown might not reflect all executions of a statement.
The SQL Archiver is turned on by default. You can turn it on or off using SQL Archive options in the connection profile for a database or partition. You can also use these options to set capacity for the SQL Archiver.
Drill down on a statement
When the Static 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 static statements.
The Static SQL Details sub drilldown is displayed at the bottom of the Static 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 Static 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
-
SQL Tuning component of Quest Central for DB2
To tune the SQL statement currently displayed in the Static SQL Details sub-drilldown
-
Click
Spotlight on IBM DB2 LUW 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 Static SQL tab
All columns available for the Static 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 |
|
Agents HWM Max |
The highest number of agents used during execution of a statement. |
|
Card Est Max |
The estimated number of rows a statement will return. The estimate is made by the SQL compiler. |
|
Consistency Token |
The consistency token for the package that contains a statement. This helps to identify the version of the package. It is useful in situations where there are multiple versions for a certain combination of package name and creator. |
|
Cost Est Max |
The maximum estimated execution cost for a statement. Cost is shown in timerons. It is determined by the SQL compiler. |
|
Cursor Name |
The name of the cursor opened for a statement. If the statement does not use a cursor, a value is not shown. |
|
Elapsed Time Max (msec) |
The total amount of time spent executing a statement. The time is shown in milliseconds. If a statement is currently executing, the value is the amount of execution time elapsed so far. |
|
Fetches Max |
The total number of successful fetches performed during all observed executions of a statement. |
|
Int Rows Deleted Max |
The total number of rows deleted from the database due to internal activity caused by a statement. The activity can occur when the statement activates triggers on the table being referenced. It can also occur due to a cascading delete that enforces an ON CASCADE DELETE referential constraint. If the number of internal deletes is high, check to see whether the triggers and constraints defined for the table are necessary. |
|
Int Rows Inserted Max |
The total number of rows inserted in a database due to internal activity caused by a statement. The activity occurs when the statement activates triggers on the table being referenced. If the number of internal inserts is high, check to see whether the triggers defined for the table are necessary. |
|
Int Rows Updated Max |
The total number of rows updated in the database due to internal activity caused by a statement. The activity can occur when the statement activates triggers on the table being referenced. It can also occur due to a SET NULL row update that enforces an ON DELETE SET NULL referential constraint. If the number of internal updates is high, check to see whether the triggers and constraints defined for the table are necessary. |
|
Last Agent ID |
The unique identifier of the agent that was last used during execution of a statement. |
|
Last Node |
The number of the database partition on which a statement was last executed. |
|
Last Sampled |
The time a database or partition was last sampled by a statement. The timestamp includes year, month, date, and hour, in that order. |
|
Num Agents Max |
The maximum number of agents used during execution of a statement. |
|
Overflows Max |
The total number of sort overflows for a statement. These are sorts that ran out of sort heap and might have required disk space for temporary storage. The I/O required in writing to disk imposes additional overhead. |
|
Package |
The name of the package that contains a statement. |
|
Parallelism Max |
The maximum degree of intra-partition parallelism set for the database manager. This determines the maximum number of parallel operations a statement can use in a database partition. A number is shown if the maximum is user specified. ANY is shown if maximum is set to the default value. In this case, degree of parallelism is determined by the optimizer. |
|
Rows Read Max |
The total number of rows read during all executions of a statement. |
|
Rows Written Max |
The total number of rows changed (inserted, deleted, or updated) during all executions of a statement. |
|
Samples |
The number of times the SQL Archiver has sampled static SQL statements. (This value corresponds to the number of Spotlight refreshes that have occurred since the SQL Archiver was started.) |
|
Schema |
The authorization ID of the user who pre-compiled the application that contains a statement. |
|
Section |
The internal section number in the package that contains a statement. |
|
Sort Time Max (msec) |
The amount of time needed to execute sorts during executions of a statement. Time is shown in milliseconds. |
|
Sorts Max |
The total number of sorts performed for all executions of a statement. |
|
Sys CPU Time Max (msec) |
The total system CPU time used for all executions of a statement. The time is shown in milliseconds. A long system CPU time could mean that a statement would benefit from tuning. |
|
User CPU Time Max (msec) |
The total user CPU time used for all executions of a statement. The time is shown in milliseconds. A long user CPU time could mean that a statement would benefit from tuning. |
|
Version |
The version identifier for the package that contains a statement. This value is useful in situations where the version of a package was determined at precompile time for an embedded SQL program with the VERSION keyword. If a version was not specified at precompile time, a value is not shown. |
Sub drilldown tabs
Welcome to Spotlight on IBM DB2 LUW (Linux, Unix, and Windows). The help topics in this book cover features available in the interface when Spotlight is connected to a version 9 database.
Spotlight is a powerful database monitoring and diagnostic tool. Its unique user interface provides you with an intuitive, visual representation of the activity on the DB2 databases and database partitions that you choose to monitor. Graphical flows and line and fill graphs illustrate the activity on and between database components. Icons display the value of key statistics. Using the Spotlight browser, you can switch your monitoring focus between the various databases and partitions. Additionally, you can view activity at the DB2 instance level for any of these systems.
The power of Spotlight lies in its ability to provide visual and audible warnings if the performance metrics exceed acceptable thresholds. The components and dataflows on the home page change color to show you the source of the problem.
A range of reports and graphs provide you with detailed information about a DB2 database, database partition, or the instance to which the database belongs. This information can be viewed on the screen or be printed.
You can set Spotlight options to warn you when a threshold is reached. You can define a number of thresholds so that warning messages are displayed well before the traffic levels into or out of databases become critical.
Spotlight uses a number of different techniques to warn you when your DB2 instance or a database is exceeding a threshold. For example, to issue a warning, you can configure Spotlight to change a color on the home page or drilldown, provide an audible signal, or perform an action, such as send an email message.
Dynamic SQL Details tab
The Dynamic SQL Details sub drilldown provides additional information for the SQL statement selected in the Dynamic SQL tab of the Top SQL drilldown. The sub drilldown contains three panes:
-
Statement pane—This pane shows the selected statement. If you want to tune the statement, you can open a tuning session in SQL Optimizer for DB2 by clicking the SQL Tuning icon.
-
Dynamic SQL Details pane—This pane shows details that provide an indication of the impact of the selected statement on the database and system resources. The details include the number of rows deleted, updated, or inserted internally due to activity caused by the statement. They also include number of statement sorts and sort overflows, sort time, and rows read and written.
-
Dynamic SQL Reads pane—This pane shows counts for the different types of reads performed during execution of the selected statement.
From this sub drilldown, you can also open a tuning session on the statement.