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 transaction ST04 and choosing Performance ® SQL Cache in the navigation frame of the DBA Cockpit.

If you have not taken a snapshot before, the following information is displayed:

Total Cache Sum

Field	Description
Execution Time	Total execution time of an SQL statement in milliseconds
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.

The following functions are available:

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 and the Selection Criteria dialog box appears.

Set Selection Criteria

If a snapshot already exists, that is, if you had already chosen Refresh at least once, you can display the last snapshot taken. When you choose Set Selection Criteria, the Selection Criteria dialog box appears.

Depending on your system, the snapshot can give you a wide range of information, which might lead to a very large result set. 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
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 OK, 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
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.
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	Total Execution Time (in milliseconds) divided by Executions
Total User CPU Time	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	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 Sort	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.

To display the ABAP source program where the statement was defined, choose Source. An editor screen appears, which contains the related source.

See also

The EXPLAIN Function