Performance: SQL Cache Snapshot (DB2 UDB for UNIX and Windows) (SAP Library

Performance: SQL Cache Snapshot (DB2 UDB for UNIX and Windows)

The SQL Cache Snapshot displays information on SQL statements that are executed very often and stored in the SQL cache of your system. This information helps you to 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.

You can access the SQL Snapshot screen by calling the DBA Cockpit and choosing Performance ® SQL Cache in the navigation frame of the DBA Cockpit.

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, the Selection Criteria dialog box appears where you can limit the result set displayed according to the following selection criteria:

Field	Description
Executions	Number of times a statement has been executed
Total Execution Time	Total 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 (Case-Sensitive)	Search using either the wild card "*" or using a text string, for example, INSERT, to limit the number of statements displayed
Maximum Number of Rows	Number of rows to be displayed

When you have made your selections and chosen This graphic is explained in the accompanying text Continue, the result set is determined by filtering the snapshot results according to the selection criteria and the following information is displayed:

Field	Description
*Total Cache Sum*
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 under Total Cache Sum table refers to the entire SQL cache and not only to the currently selected result set.

The result set is displayed in a table:

Column	Description
SQL Text	Text of a dynamic SQL statement that was in the SQL cache at the time of the snapshot
User	Name of the database connect user who executed the statement.
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 efficiently used or whether indexes are missing. For RFC-monitored systems only: To display the ABAP source program in which the statement was defined, choose Source. An editor screen appears that contains the related source.
Total Execution Time	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)	Total Execution Time (in milliseconds) divided by Executions
Total User CPU Time (ms)	Total user CPU time in milliseconds for a statement. This value together with the total execution time gives you information on the longest running statements.
Total System CPU Time (ms)	Total system CPU time in milliseconds for a statement. This value together with total execution time and total user CPU time helps you to identify statements that use an excessive number of resources.
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 in order to obtain the result set.
Rows Read (%)	Rows Read divided by Total Cache Sum Rows Read
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.
Rows Written (%)	Rows Written divided by Total Cache Sum Rows Written
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 statement was executed.

Note

If no hits are found, the result set is empty and nothing is displayed.

The following functions are available for further actions:

· This graphic is explained in the accompanying text Refresh

Retrieves the snapshot for the partition and the system you have selected in the SQL Cache Snapshot group box. When this is executed successfully, the current system date and time is displayed in the same group box in the Last Snapshot field. Set Selection Criteria now becomes active again and the Selection Criteria dialog box appears with the selection criteria preset with the previous selection criteria.

· This graphic is explained in the accompanying text Set Selection Criteria …

When you choose This graphic is explained in the accompanying text Set Selection Criteria, the Selection Criteria dialog box appears again and you can make further evaluations based on the already taken snapshot data.

· This graphic is explained in the accompanying text Source

To display the ABAP source program where the statement was defined, choose This graphic is explained in the accompanying text Source. An editor screen appears, which contains the related source.

Note

This function is only available for RFC-monitored systems.

· This graphic is explained in the accompanying text EXPLAIN

To display detailed performance analysis, you can display the access plan for the SQL statement by choosing This graphic is explained in the accompanying text EXPLAIN. For more information, see The EXPLAIN Function.