4 Performance
4.5 Performance: Snapshots
4.5.10 Snapshots: SQL Cache (Only DB2 V9.5 or Lower)
Note
This snapshot option is only available if one of the following applies: The currently monitored database is DB2 V9.5 or lower.
The currently monitored database is DB2 V9.7 or higher and the database
configuration parameterMON_ACT_METRICS is set to NONE whereas the database manager configuration parameter DFT_MON_STMT is set to ON.
End of the note.
The SQL Cache screen displays information about SQL statements that are executed very often and stored in the SQL cache of your system. This information helps you identify those SQL statements that consume a large number of resources. You can also determine whether fine-tuning of those statements is necessary to improve the performance of the database. Depending on your system, the snapshot can give you a wide range of information, which might lead to a very large result set. After the snapshot has been taken and before the results are displayed, you can specify filter criteria in the Selection area to limit the result set:
Field Description
Executions Number of times a statement has been executed
Total Execution
Time Total execution time in milliseconds for a statement Avg. Execution
Time Average execution time in milliseconds for a statement Rows Read Number of rows read for a statement
Rows Written Number of rows written by a statement
SQL Text Search using either the wild card "*" or using a text string, for example,
INSERT, to limit the number of statements displayed
Rows Read / Rows Processed
Ratio of rows read from the base table compared to rows processed, which can be either rows returned to the application (SELECT statements) or rows written (UPDATE, INSERT, DELETE statements)
A value of 1 indicates an optimal access to the requested data. High values indicate statements with an inefficient access.
Note
This metric is only evaluated if your database is DB2 V9.5 Fix Pack 1 or higher.
Field Description
End of the note.
If you choose the Apply Selection pushbutton, the result set is determined by filtering the snapshot results according to your selection criteria and the following information is displayed in the Summary area:
Column Description
Execution Time Total execution time in milliseconds for an SQL statement
Rows Read Total number of rows read
Rows Written Total number of rows written
Note
The data displayed in the Summary area refers to the entire SQL cache and not only to the currently selected result set.
End of the note.
The result set is displayed in a table as follows:
Column Description
SQL Text Text of a dynamic SQL statement that was in the SQL cache at the time of the
snapshot
Executions
Number of times a statement was executed
This value helps you to identify which statements are executed very often. A high number of executions does not necessarily mean that a statement is using an excessive amount of resources. You should also check the number of rows read and rows written. If you find relatively high values here, choose
EXPLAIN to check whether indexes are not being used efficiently or whether
indexes are missing.
Total Execution Time (ms)
Total execution time in milliseconds for a statement
You can use this value together with Executions to identify the statements that would benefit from further analysis.
Total Execution Time ( %)
Total Execution Time (milliseconds) divided by Total Cache Sum Execution Time (milliseconds)
Average Execution Time (ms)
Buffer Quality
(%) Buffer quality for this statement in percent
Rows Read / Rows Processed
Ratio of rows read from the base table compared to rows processed, which can be either rows returned to the application (SELECT statements) or rows written (UPDATE, INSERT, DELETE statements)
A value of 1 indicates an optimal access to the requested data. High values indicate statements with an inefficient access.
Note
This metric is only available if your database is DB2 V9.5 for Linux, UNIX, and Windows Fix Pack 1 or higher.
End of the note.
BP Gets / Rows Processed
Average number of pages read from the buffer pool per rows processed, which can be either rows returned to the application (SELECT statements) or rows written (UPDATE, INSERT, DELETE statements)
Note
This metric is only available if your database is DB2 V9.5 for Linux, UNIX, and Windows Fix Pack 1 or higher.
End of the note.
BP Gets / Executions
Average number of pages read from the buffer pool per execution of the statement
Rows Read
Number of rows read
You can use this value to identify statements that would benefit from
additional indexes. Use EXPLAIN to analyze the statement. The given value does not necessarily correspond to the number of rows of the result set of the SQL statement. The Rows Read value shows the number of rows that needs to be read to obtain the result set.
Rows Written
Number of rows that were changed (inserted, deleted, or modified) in a table High values might indicate that you should update statistics using RUNSTATS.
SQL Sorts
Number of sorts that were necessary to execute the statement
You can use this value to determine whether new indexes are needed. Use
EXPLAIN to check whether and which indexes were used when the selected
Sort Overflows Number of sort overflows
Total Sort Time (ms) Total sort time in milliseconds
Note
If no hits are found, the result set is empty, and nothing is displayed. End of the note.
The following functions are available for further actions:
EXPLAIN
To display a detailed performance analysis, you can display the access plan for the SQL statement by choosing the EXPLAIN pushbutton.
For more information, see The EXPLAIN Function [page 43].
Index Advisor
To improve the performance of a query, you can retrieve recommendations about useful indexes using the Index Advisor pushbutton. In addition, you can design new virtual indexes that can be validated before they are actually created.
For more information, see The Index Advisor [page 50].
Source
To display the ABAP source code from where the SQL statement was executed, you can choose the Show Source pushbutton.
Note
The Show Source pushbutton is only available if the following applies:
o The SAP system on which the currently monitored database is running is an ABAP system.
o The SAP system has the minimum Support Package level as described in
SAP Note 1493490.
End of the note.
Displaying Details About SQL Statements
To display detailed information about an SQL statement, select a line from the result list. In the content detail area, the following tab pages are displayed:
Common
Resource Consumption I/O
Common
On the Common tab page, you can find details about the statements and rows. These details include, for example, information about the following:
Package cache ID Insert time stamp Partition
Authorization ID Buffer pool quality Executions Compilations
Rows deleted, inserted, and read
Resource Consumption
The Resource Consumption tab page displays details about the total resource consumption, the proportional resource consumption, and the sorts.
These details include, for example, information about the following: Execution time
User CPU time System CPU time
Worst and best preparation time Rows read and written
Average time per row read and per row written Sort overflows
Total sort time
I/O
The I/O tab page displays details about the buffer pool, temporary data, and XML storage, that is, about the number of logical and physical reads.