Jan 11, 2013 12:17:00 PM by Steve Hilker
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 |
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 Note: A large SGA increases processing time. |
Collection Time Page | Description |
Collection Time |
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.
Tags: Toad for Oracle SQL Optimizer for Oracle Wiki Tutorial
Written by Steve Hilker
Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.
In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.