Viewing the SQL statements in the dynamic SQL cache

This topic describes how to view the SQL statements in the dynamic SQL cache.

You can view detailed information about the SQL statements in the dynamic SQL cache as follows:

In the folders pane of the Statistics Details window, click Dynamic SQL Statements and then click SQL Statements.
The SQL Statements pane is displayed without any entries.
To display the SQL statements that are in the cache:
1. Click the Filter () toolbar button to check the filter values and set the appropriate values for your subsystem. For more information about defining filter criteria, see step 5.
2. Select the Receive Statement Cache information check box.
  The list is refreshed whenever you click View > Refresh or when an automatic refresh occurs.
The SQL statements in the cache might be shown with the value 0 for all counters because no trace data has been collected yet. To get all values for the counters, click the Start () toolbar button to start the collection of the specified counter values.
After the next refresh, the counter values of all SQL statements displayed reflect the values collected since the trace started.

Trace data collection continues until you click the Stop () toolbar button. Because tracing can increase the overhead, stop tracing when you have finished working with the SQL statements. You can only stop the traces that you started.
To view the text of an SQL statement, double-click the statement.
The SQL Statements text page opens, where text stands for the first couple of characters of the statement text.

Note: If the text is much longer than the space provided and therefore difficult to read, click View Statement in New Window to view the text in another window.

This page contains the text and the counters of the SQL statement. If you have an Explain product installed and the statement contains a SELECT, UPDATE, INSERT, DELETE, or VALUES clause, you can click Explain to view the access path. For more information about Explain products, refer to Using Explain products to analyze and improve SQL statements.
To reduce the list of SQL statements:
1. Return to the SQL Statements pane of the Statistics Details window.
2. Click the Filter () toolbar button.
  The Dynamic Statement Cache Filter Criteria window is displayed.
3. Choose your filter method:
  - If you want to list only those SQL statements where a specific counter exceeds a certain value:
    Click Statements with value higher than, specify a value, and then select one of the counters listed under Fields.
  - If you want to display the SQL statements sorted by the values of a counter in descending order:
    Click Statements with highest value and then select one of the counters listed under Fields.
    
    This option is useful, for example, if there are more SQL statements in the cache than can be displayed (up to 1 MB). For example, if there are 5 MB of SQL statements in the cache and you are mainly interested in those with high Getpage counter values, ensure that these SQL statements are displayed by using this option.
4. Click OK to return to the SQL Statements pane.
  The list of SQL statements is changed according to your filter criteria. The filter remains valid until you change it.
Restriction: If you are viewing history data, you cannot define a filter. For more information about history data, refer to Viewing history data.
To change the order of the table columns shown or hide the table columns that you are not interested in, click the Customize Columns () toolbar button. The Customize Columns window is displayed.
For further information about how to customize columns, see Customizing table columns.