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
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.
Click to select a group or click to create a new group in the Group list.
Click . The Add Inspect SGA Job wizard displays.
Complete the following fields in the wizard:
General Information Page Description
Select the Executed SQL from SQL Area option. Collecting Criteria Page Description
Select the Top n records option and enter the number of records to display.
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
Select the Start collecting when you click the Inspect button option.
Click to retrieve the SQL statements and run time statistics.
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.