Chat now with support
Chat with Support

SQL Optimizer for Oracle 10.0 - 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

Filter Database Objects (Scan SQL)

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

To filter a database object

  1. Click in the Database Objects page of the Add Scanner Jobs wizard.

  2. Review the following for additional information:

    User Click and select the database object owner.
    Object Type Click and select a database object type.
    Filter

    Enter the filtering criteria using the % wildcard and click .

    Important: The filtering criteria is case sensitive so you must match the uppercase or lowercase characters of the database object name.

    Notes:

    • The % is used to represent multiple characters.
    • Select the Exclude filter checkbox to exclude database objects that meet the filtering criteria instead of including them.

  3. Select the database objects to add.

 

Related Topic

About Scanning SQL

Scan SQL

Manage Jobs (Scan SQL)

Scan SQL displays scan jobs you create using the Add Scanner Jobs wizard in the Job List pane by groups. You can use the Job List pane to manage scan jobs for a selected group.

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

To manage a job

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

  2. Click beside the Group list and select a group.

    Tips:

    • Click beside the Group list to create a new group.

    • Right-click a job in the Job List pane and select Scan | option to start scanning.

    • You can change the schema by clicking the entry in the Set Schema column and selecting a different schema before scanning a job.

    • Select the Eliminate Duplicate SQL checkbox to eliminate duplicate SQL statements while scanning.

    • Right-click a job and select Delete Jobs | option to delete jobs.

  

Related Topics  

About Scanning SQL

Scan SQL

View SQL Classification

View SQL Text (Scan SQL)

View_SQL_Classification

Scan SQL extracts syntactically correct SQL statements and retrieves their execution plans. Scan SQL then classifies the extracted statements as Simple, Complex, Problematic, or Invalid.

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

To view SQL Classification

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

  2. Click beside the Group list and select a group.

  3. Select a scan job in the Job List pane to display SQL Classification information in the SQL List pane. Review the following for additional information:

    Classification Description

    Problematic

    SQL statements are classified as problematic when Scan SQL determines they should be optimized. Problematic SQL statements have execution plans that contain one or more of the following:

    • 6 or more references to database objects.
    • 2 or more full table scans.
    • A full table scan in a step where the bytes is four megabytes or more.
    • A full table scan in a step where the cardinality is 50,000 or higher.
    • A nested loop in step where the bytes is one megabytes or more.
    • A nested loop in a step where the cardinality is 1,000 or higher.

    Notes:

    • These are the default rules for classifying a SQL statement as problematic.

    • You have the option to include or exclude the SYS.DUAL table in each definition.

    Complex

    SQL statements are classified as complex when the execution plan does not meet any of the rules for the problematic SQL classification but contain one or more of the following:

    • 2 or more references to database objects.
    • 3 or more fast full index scans.
    • A full table scan in a step where the bytes is two megabytes or more.
    • A full table scan in a step where the cardinality is 10,000 or more.

    Notes:  

    • These are the default rules for classifying a SQL statement as complex.

    • You have the option to include or exclude the SYS.DUAL table in each definition.

    Simple

    SQL statements are classified as simple when the execution plan does not meet any of the rules for the problematic or complex SQL classifications.

    Invalid

    SQL statements are classified as invalid when Scan SQL cannot retrieve an execution plan for one of more of the following reasons:

    • The current user does not have privileges to the tables, views, or other database objects referenced in the SQL statement.
    • The SQL statement is dynamically constructed at run time.
    • The database object references in the SQL statement does not exist.
    • The schema used to retrieve the execution plan was not the correct schema for the SQL statement.

    Tips:

    • Click to group the SQL statements by classification.
    • Click to list the SQL statements numerically.
    • 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  

About Scanning SQL

Scan SQL

Manage Jobs (Scan SQL)

View SQL Text (Scan SQL)

View SQL Text

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

To view SQL text

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

  3. Select a job in the Job List pane.

  4. Select a SQL statement in the SQL List pane to display the SQL text.

  

Related Topics  

About Scanning SQL

Scan SQL

Manage Jobs (Scan SQL)

View SQL Classification

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating