Toad World Blog

Inspect SGA

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

  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 3125.TB_DownArrow.png-550x0 to select a group or click 8422.TB_Ellipses.png-550x0 to create a new group in the Group list.

  3. Click TB_AddScannerJobs.png-550x0. 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 3125.TB_DownArrow.png-550x0 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 TB_InspectSGA.png-550x0 to retrieve the SQL statements and run time statistics.

  6. Select a statement that requires optimization in the SQL Statistics pane and click TB_SendtoOptimizer_InspectSGA.png-550x0.

    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

Steve Hilker

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.