Toad World Blog

Scan SQL

Aug 28, 2013 12:49:00 PM by Steve Hilker

Scan SQL helps you identify problematic SQL statements in your database environment by automatically extracting statements embedded in database objects, stored in application source code and binary files, captured from Oracle's System Global Area, or saved in Foglight Performance Analysis repositories. Scan SQL retrieves and analyzes execution plans for the extracted statements and classifies them according to complexity. You can then send statements that Scan SQL classifies as problematic or complex to Optimize SQL.

To scan SQL

  1. Select the Scan SQL tab in the main window.

  2. Click 3125.TB_DownArrow.png-550x0 to select a previously created group or click 8422.TB_Ellipses.png-550x0 to create a new group for your scan job.

    Note: Scan SQL helps you manage scan jobs by organizing them into groups.

  3. Click TB_AddScannerJobs.png-550x0. The Add Scanner Jobs window displays.

  4. Review the following for additional information:

    Database Objects Page Description

    Database objects

    Select a schema, database object type, or individual database object, and then click 4721.TB_AddDatabaseObjects.png-550x0 to add the object.

    Tip: Click 8422.TB_Ellipses.png-550x0 to browse for database objects.

    Source Code Page Description

    Source code type

    Select Text/Binary files, Oracle SQL *Plus Script, or COBOL programming source code to indicate the source code type for the file or directory you want to scan.

    Add by file

    Click 6303.TB_AddSourceCode.png-550x0 and browse to the files you want to add.

    Add by directory

    Click 6303.TB_AddSourceCode.png-550x0 and browse to the directories you want to add.

    Note: Select the Include Sub-directory checkbox to scan sub-directories.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

    Inspect SGA Page Description

    Group

    Select the Inspector group that contains the SQL statements you want to scan.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

    Foglight Performance Analysis for Oracle Page Description

    Select a database to search for the repository used to store captured SQL

    Click 3125.TB_DownArrow.png-550x0 to select a previously created database connection, and then click Check for PA Repository to locate the repository.

    Tip: Click 8422.TB_Ellipses.png-550x0 to open the Connection Manager to create a new connection.

    Scan using schema

    Click 4812.TB_DownArrow and select a schema to scan.

     

  5. Click Finish to start scanning.

  6. Select a scan job from the Job List pane to view additional information.

    Details displayed in the Job List pane include the number of SQL statements found and the classification for each statement.

    Tip: Click 4812.TB_DownArrow and select a different group to display scan jobs from a different group.

  7. Select a SQL statement in the SQL List pane to view additional information for the selected statement in the SQL Text and Execution Plan panes.

    Tip: Click TB_SentoOptimizer_ScanSQL.png-550x0 in the SQL Text pane to send the selected statement to Optimize SQL.

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.