SGA Trace/Optimization

The SGA Trace/Optimization window displays SGA information for you so that you can easily optimize your database.

You can view information, utilize client-side filtering of the grid, and check session currently executing the selected query.

To access SGA Trace/Optimization

·         Select the Database|Monitor|SGA Trace/Optimization menu item.

Use this window to view information about SQLs executed and how they performed.

Requires access to the V$ Oracle Dictionary views

There are four tabs in this dialog box:

·         SQL Shared Pool

·         Execution Stats

·         SQL

·         Explain Plan

Statistics Area

In the statistics area you can view Execution stats and the SQL within the shared pool. In addition, you can:

·         Search for SQL - enter text of the SQL you want to limit the shared pool area to SQL starting with the letters you enter.

·         Hide Zero Stats - when checked any stats with a value of 0 are hidden in the execution stats area.

·         Select an Instance - if you are on a RAC system, you can limit the display to a particular RAC instance.

Execution Stats

Execution stats are displayed in the left side panel. This panel shows information about the selected SQL statement in the SQL Shared Pool tab.

SQL Shared Pool

The Shared Pool SQL is displayed at the top of the right hand side. This panel shows information about the SQL Shared Pool. You can also filter the SQL statements you want to view using the SGA Trace Toolbar

SQL tab

This shows the entire SQL for the selected SQL statement in the SQL Shared Pool tab.

If you get the "SQL Body Unavailable" message when clicking on the SQL tab, then the SQL is not present in Oracle's SGA (System Global Area), which is a pool of the most recently used SQL statements. Not all SQL statements can be retained in the SGA forever, because it is a limited size. The least frequently used statements are discarded in favor of new ones.

From the toolbar on this tab you can do the following:

Explain Plan tab

This shows the Explain Plan for the selected SQL statement in the SQL Shared Pool tab. The total cost of the statement is displayed in the Total cost for statement label. If the cost information is not available because of rule-based optimization, then this label will be blank. You can also right-click and select Explain Plan options for this Explain Plan.

Sessions tab

The sessions tab displays any active sessions that remain in the SGA pool.