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

Tutorial: Optimize SQL (Plan Control)

Using Plan Control mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates execution plan alternatives for your SQL statement without changing the source code. You can then execute the alternatives to retrieve run time statistics and identify the best alternative for your database environment. In the second step, you can use Plan Control mode to deploy the execution plan to the Manage Plans module as an Oracle plan baseline.

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

Step 1: Generate and Execute Execution Plan Alternatives

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

  2. Select Plan Control from the Optimize SQL start page.

    Note: If the start page does not display, click the arrow beside and select New Plan Control Session.

  3. Enter a SQL statement in the Original SQL pane.

    Tip: Select the This SQL is contained inside a PL/SQL block checkbox if your SQL statement originated from a PL/SQL block. Selecting this checkbox ensures that the SQL text for the baseline you create matches the SQL text in your database.

  4. Click to generate alternative execution plans for your SQL statement. The Select Connection and Schema window displays.

  5. Select a connection and schema to use.

  6. Click to execute all alternative execution plans to retrieve run time statistics.

  7. Review the run time statistics in the Plans pane to identify the best alternative.

Step 2: Deploy Execution Plan as a Baseline

  1. Click .

  2. Review the following for additional information:

    Deploy Description

    Select a plan to deploy

    		 Click and select an execution plan alternative to deploy as a baseline plan.

    Performance comparison

    		 Use this to review the performance of selected plans against the original.

    Mark the plan as

    Review the following for additional information:

    • Enabled—Select whether to enable or disable this plan.
    • Fixed—Select whether to deploy this plan as fixed or non-fixed.
    • Not Auto-Purged—Select whether to auto-purge when it is not used.

    Plan name

    		 Enter a name for the plan.

    Description

    		 Enter a description for this plan.
  3. Click to deploy the plan to Manage Plans.

  

Related Topics

About Optimizing SQL (Plan Control)

About Managing Plans

 

Tutorial: Batch Optimize SQL

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

To batch optimize SQL

  1. Select the Batch Optimize SQL tab in the main window.

  2. Click Add Code to Optimize in the Batch Job List pane and select All Types. The Add Batch Optimize SQL Jobs window displays.

  3. Review the following for additional information:

    Connection Page Description

    Connection

    Click to select a previously created database connection.

    Tips:

    • Click to open the Connection Manager to create a new connection.

    • You can select an alternative connection for executing the SQL statement alternatives Batch Optimize generates.

    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.

    • Your database privileges determine if you can scan all selected database objects.

    Execute using schema

    		 Click and select an alternative schema for executing the SQL statement alternatives.
    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.

    Execute using schema

    		 Click and select an alternative schema for executing the SQL statement alternatives.
    SQL Text Page Description

    SQL text

    		 Enter SQL statement text.

    Scan using schema

    		 Click and select a schema to scan.

    Execute using schema

    		 Click and select an alternative schema for executing the SQL statement alternatives.
    Scan SQL Page Description

    Group

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

    Scan using schema

    Click and select a schema to scan.

    Execute using schema

    		 Click and select an alternative schema for executing the SQL statement alternatives.
    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.

    Execute using schema

    		 Click and select an alternative schema for executing the SQL statement alternatives.

    Note: Batch Optimize helps you manage jobs by organizing them into batches. Use the Batch Info page to create a new batch or to add the current job to an existing batch.

  4. Click Finish to start batch optimization.

    Batch Optimize SQL scans the job you created, classifies and optimizes the statements, and executes the SQL statement alternatives it generates.

    Notes: 

    • Scanning starts automatically if you select the Automatically start extracting SQL when job is added checkbox in the Batch Optimize SQL options page. Batch Optimize SQL selects this checkbox by default.

    • Batch Optimize SQL selects the SQL statement to optimize based on the classification types selected in the Batch Optimize SQL options page. Batch Optimize SQL selects Problematic and Complex SQL classification types by default.

    • Batch Optimize SQL executes SQL alternatives it generates based on the statement types selected in the Batch Optimize SQL options page. Batch Optimize SQL selects SELECT statements by default.

  5. Select Batch List in the Batch Job List pane to view information about the jobs you created.

    The Batch List pane sorts information about your jobs by batches. Additional information displays in the Jobs Improved pane.

  6. Select a batch from the batch list node to see details for the batch in the Job List pane.

    The Job List pane displays the type of job, job status, and time of improvement for each job in the batch. Additional information displays in the SQL Classification and Cost and Elapsed Time Comparison panes for a selected job.

    Tip: Select a job in the Job List pane and click to generate a replacement script with the optimized SQL statement.

  7. Select a job from the batch node to see details of the job.

    The SQL List pane displays SQL classification information for the SQL statements in the job you select. The Original SQL Text and Best Alternative SQL Text panes allow you to compare your original SQL statement with the best alternative Batch Optimize SQL generated.

    Tip: Select a SQL statement in the SQL List pane and click to send the statement to Optimize SQL and view all SQL alternatives.

 

Related Topics

About Batch Optimize

Tutorial: 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, or captured from Oracle's System Global Area. 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.

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

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:

    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. 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.

 

Related Topics

About Scanning SQL

Tutorial: Inspect SGA

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 to select a group or click to create a new group in the Group list.

  3. Click . 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 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 to retrieve the SQL statements and run time statistics.

  6. Select a statement that requires optimization in the SQL Statistics pane and click to send to Batch Optimize SQL or click the arrow beside and select an option.

    Tip: You can add an Inspect SGA job in Batch Optimize SQL to optimize all the SQL statements in the collection.

 

Related Topics

About Inspect SGA

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating