Top SQL Drilldown 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 SAP ASE
-
SQL Tuning component of Quest Central for DB2
To tune the SQL statement currently displayed in the Static SQL Details sub-drilldown
-
Click
on the left side of the Static SQL Details sub drilldown.
Spotlight® on SAP ASE 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. |