Inspect SGA

Inspect SGA retrieves executed SQL statements from Oracle’s System Global Area or currently running SQL statements from Oracle’s open cursor. Once you retrieve the statements, Inspect SGA displays the statements and their run time statistics so you can identify resource intensive statements in your database environment.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To retrieve a previously executed SQL statement

1. Select the Inspect SGA tab in the main window.

   Note: To retrieve previously executed SQL statements, you must have privileges to view SYS.V_$SQLAREA and either SYS.V_$SQLTEXT_WITH_NEWLINES or SYS.V_$SQLTEXT.

2. Click  to select a group or click  to create a new group in the Group list.

3. Click . The Add Inspect SGA Job wizard displays.

4. Complete the following fields in the wizard:

   General Information Page	Description
   Job type	Select the Executed SQL from SQL Area option.
   Collecting Criteria Page	Description
   Collecting Criteria	Select the Top n records option and enter the number of records to display.
   First by	Click  and select the statistic to use to extract SQL statements if you are not displaying all records. Note: A large SGA increases processing time.
   Collection Time Page	Description
   Collection Time	Select the Start collecting when you click the Inspect button option.

5. Click  to retrieve the SQL statements and run time statistics.

6. Select a statement that requires optimization in the SQL Statistics pane and click .

   Tip: You can add an Inspect SGA job in Batch Optimize to optimize all the SQL statements in the collection.