Scan SQL

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  to select a previously created group or click  to create a new group for your scan job.

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

3. Click . 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  to add the object. Tip: Click  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  and browse to the files you want to add.
   Add by directory	Click  and browse to the directories you want to add. Note: Select the Include Sub-directory checkbox to scan sub-directories.
   Scan using schema	Click  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  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  to select a previously created database connection, and then click Check for PA Repository to locate the repository. Tip: Click  to open the Connection Manager to create a new connection.
   Scan using schema	Click  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  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  in the SQL Text pane to send the selected statement to Optimize SQL.