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

Rewrite SQL

About Optimizing SQL (SQL Rewrite)

Use SQL Rewrite sessions to optimize SQL statements by using SQL Optimizer's Artificial Intelligence engine to rewrite SQL statement syntax. This process begins with SQL Optimizer analyzing your original SQL statement and generating a list of semantically equivalent statement alternatives. The SQL rewrite process continues until SQL Optimizer cannot generate additional alternatives. SQL Optimizer then applies a set of Oracle hints to each alternative to generate additional alternatives.

Once SQL Optimizer completes the SQL rewrite process, you can test run the alternatives to retrieve execution plans and run time statistics. You can compare execution times and run time statistics for alternatives to identify the best statements for your database environment.

Notes:

  • Although different SQL statement alternatives produce the same results, Oracle may use different paths to retrieve data for each alternative. The database structure, indexes, and data volumes affect execution statistics. Therefore, you should test the alternatives with your database environment to find the best alternative.

  • The Intelligence Level setting you select affects the duration of the optimization process and the number of alternatives SQL Optimizer generates.

 

Related Topics

About Generating Index Alternatives

About Optimizing SQL (Plan Control)

Select Connection

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

To select a connection

  1. Click .

  2. Review the following for additional information:

    Select Connection Description

    Connection

    Click to select a previously created connection.

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

    Select Schema Description

    Schema

    		 Click to select a schema for the connection.

Related Topics

Create Optimize SQL Sessions

Optimize SQL Statements

You can use SQL Rewrite Sessions in Optimize SQL to generate semantically equivalent SQL alternatives with unique execution plans from your original SQL statement.

You can then test run the alternatives to retrieve execution times and other run-time statistics for identifying the best SQL alternative for your database environment.

To learn how to optimize SQL statements and test run alternatives all in one step, see Automatically Optimize SQL Statements.

To generate and test run alternative SQL statements

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

  2. Select SQL Rewrite from the Optimize SQL start page.

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

  3. Enter a SQL statement in the Alternative Details pane.

  4. Click to retrieve the execution plan for your SQL statement.

  5. If you did not already select a connection for this session, the Select Connection and Schema window displays. Select a connection and schema to use.

  6. (Optional) When you create a new SQL Rewrite session, your global default Intelligence Level settings are displayed in the top-right corner of the session window. To change settings for the current session only, click or .

    Changing settings for the current session leaves the global settings unchanged. To specify global default values, see Intelligence (Optimize SQL).

  7. Click to optimize (rewrite) the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.

  8. When SQL Optimizer is finished generating SQL alternatives, you can test run one or more alternatives to collect run-time statistics. Use one of the following methods:

    • To test run a single SQL alternative, select the alternative and click .
    • To test run all SQL alternatives, click the drop-down arrow beside and select Test Run - All.
    • To test run multiple alternatives simultaneously, select the alternatives. Then click the drop-down arrow beside and select Test Run - Selected. For more information about the other test run options, see Test Run SQL Alternatives.

    Note: The statements are test run against the database during this process to obtain accurate run-time statistics. SQL Optimizer automatically rolls back any changes made to the database after it executes SELECT, SELECT INTO, INSERT, DELETE, or UPDATE statements.

  9. If you selected multiple or all alternatives, the Test Run Settings dialog opens. Select criteria to apply to this test run. See Test Run Settings for more information about selecting test-run options.
  10. When the SQL alternatives are finished running, the run-time statistics display in the Alternatives pane. Select an alternative to see more information in the SQL Text and Execution Plan panes.

Tips:

To clear optimization results

  • Click the drop-down arrow beside and select one of the following:
    • Keep Original Scenario—Select to clear only the results and retain both the original SQL statement and the database connection.
    • Clear Original Scenario—Select to clear the SQL Rewrite window, but retain the database connection.

 

Related Topics

About Optimizing SQL (SQL Rewrite)

Automatically Optimize SQL Statements

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating