Chat now with support
Chat with Support

SQL Optimizer for Oracle 9.3.3 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

About Scanning SQL

Scan SQL helps identify problematic SQL statements in your database environment by automatically extracting SELECT, INSERT, UPDATE, or DELETE statements from your source code. Scan SQL retrieves and analyzes the execution plans for the extracted statements and classifies them according to complexity. You can then optimize statements classified as problematic using Optimize SQL or Batch Optimize SQL.

You can use Scan SQL to extract and analyze statements from the following areas and files:

Database Objects

Database objects with embedded SQL statements which includes stored procedures, triggers, views, rules, and defaults.

Text/Binary Files

Text and binary files that contain embedded SQL statements.

SQL*Plus Scripts

SQL*Plus scripts that contain embedded SQL statements.

COBOL Files

COBOL source code that contains embedded SQL statements.

Inspect SGA Files

Files with SQL statements from Oracle's System Global Area created in Inspect SGA.

 

Related Topics

About SQL Conversion

Scan SQL retrieves execution plans when it extracts SQL statements. If Scan SQL cannot retrieve the execution plan, it attempts to apply a SQL conversion to render the statement as a valid standalone statement. Click the following to display more information for each type of conversion Scan SQL attempts to apply

Notes:

  • If Scan SQL applies a conversion, it may be necessary to reverse the changes after optimization before you copy an alternative SQL statement back to the original source code.

  • You can review the details for conversions Scan SQL applied in the Conversion pane of the SQL Information tab. See View SQL Information (Scan SQL) for more information.

  

Related Topics

Scan SQL

Create Scan SQL Groups

This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To create a group

  1. Click beside the Group list in the main window.

  2. Review the following for additional information:

    Group Enter a name for the group.
    Connection

    Click to select an existing connection for the group.

    Tip: Click to create a new connection. See Connect to the Database for more information.

    Tip: Right-click a group and select Delete to delete the group.

  

Related Topics

Scan SQL

Scan SQL

Scan SQL extracts SQL statements embedded in database objects, stored in application source code and binary files, or saved in files created using Inspect SGA. Use the Add Scanner Jobs wizard to create scan jobs using statements from any of these sources.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To create a scan job

  1. Select the Scan SQL tab in the main window.
  2. Click beside the Group list and select a group.

    Tip: Click to create a new group. See Create Scan SQL Groups for more information.

  3. Click . The Add Scanner Jobs wizard opens.

  4. Complete the wizard to create a scan job. Review the following for additional information:

    Page Description
    Database Objects Page

    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

    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

    Group

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

    Scan using schema

    Click and select a schema to scan.

  5. Select a scan job in the Job List pane for more information.

  6. Identify problematic SQL statements in the SQL List pane. See View SQL Classification for more information.

  7. Select statements to optimize in Optimize SQL or Batch Optimize SQL. See View SQL Text (Scan SQL) for more information.

Tips:

  • You can customize a SQL name in the SQL List pane by entering a new name over the auto-generated name.
  • Click in the User Comments column to add a note about the SQL statement.

  

Related Topics  

Manage Jobs (Scan SQL)

View SQL Information (Scan SQL)

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating