Contents Lab128 - Tools for Advanced Oracler Tuning and Monitoring. Reference Guide.

SQL Area

The SQL Area window displays the content of SQL Area using v$sql or v$sqlarea views. It helps to identify the top SQL using the most server resources. The SQL text is fetched automatically for any selected entry. Explain Plan can be taken from v$sql_plan and shown in the Explain Plan window. There are many criteria to sort v$sql data on, select them in the "Order By" drop-down box.

Viewing options.

The upper part of the window provides a wide selection of options. You can choose a metric that will be used to order the records in descending order. Narrow the result set by applying filters in the "FILTERS" section. All of them are self-explanatory. Also you can filter statements by presence of the particular string. Type the string in the "SQL Text like" box. Don't worry about the case of the letters; the selection will be made in case-insensitive mode. Please keep in mind that Oracle 9i and earlier versions, column SQL_TEXT in v$sql view contains only first 1000 characters. Therefore only first 1000 characters will be searched. On Oracle 10g, entire SQL text is searched.

The SQL statements can also be filtered on attributes of their execution plan such as operation and object name. For example, if you want to find all SQL statements using full scan, select TABLE ACCESS FULL in the "Operation like" drop-down box (you can also type your own string). To filter on object name, type the name or part of it in the "Object Name like" box. For example, if you want to find statements in the SQL Area which use a particular index, type the index name in this box.

Note 1: After changing certain options, the whole snapshot should be refreshed by using the Update button. Lab128 will remind you by changing the background of the list to a reddish color - the result set is invalid for newly selected options.

Note 2: Although it is possible to re-order the SQL list clicking on the header of a column, it may not produce the intended result. When the "Update" button is pressed, the query is submitted to Oracle with the proper "ORDER BY" clause defined by the "Order By" box. Lab128 will fetch the first 130 records from the server. Reordering this result set by clicking on the header of a column only reorders that limited set.