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 Optimizing SQL (Plan Control)

Plan Control sessions use the SQL Plan Management feature introduced in Oracle 11g to optimize execution plans and deploy plan baselines for SQL statements without changing the original source code. This feature is particularly useful for third-party applications where you do not have access to the source code.

Important: You can only use Plan Control mode with an Oracle 11g or later connection.

The process begins with SQL Optimizer analyzing your original SQL statement to generate execution plan alternatives. SQL Optimizer then applies a set of Oracle hints to each execution plan alternative to generate additional alternatives. Once SQL Optimizer completes this process, you can execute the alternatives to retrieve run time statistics for each execution plan alternative and compare the alternatives to identify the best one for your database environment. You can then deploy the selected alternative as a plan baseline to the Manage Plan module.

  

Related Topics  

About Managing Plans

About Optimizing SQL (SQL Rewrite)

 

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

Generate Execution Plan Alternatives

Plan Control sessions optimize your SQL statement by generating execution plan alternatives for the statement. You can then select an alternative execution plan for your SQL statement and deploy it as a plan baseline to improve database performance.

To generate 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 is a PL/SQL checkbox to indicate 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 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 Plan Control session, your default Intelligence Level setting is displayed in the top-right corner of the session window. To change the Intelligence Level setting for the current session, click .

    When you change the setting for the current session, the default setting remains unchanged. To specify a default setting, see Intelligence (Plan Control).

  7. Click to generate execution plan alternatives.

    Note: Click to stop the plan generation process.

  8. When the generation process is finished, select a plan alternative in the Plans pane and review the plan in the Execution Plan pane.
  9. You can then test run the generated execution plan alternatives. See Test Run Execution Plan Alternatives for more information.

To clear the Plan Control window

Right-click within the SQL Text pane and do one of the following:

  • Select Clear Optimization Results | Keep Original Scenario to clear only the results and retain both the original SQL statement and the database connection.
  • Select Clear Optimization Results | Clear Original Scenario to clear the Plan Control window, but retain the database connection.

  

Related Topics

About Optimizing SQL (Plan Control)

Test Run Execution Plan Alternatives

View SQL Information (Plan Control)

Deploy Baselines

Test Run Execution Plan Alternatives

Once SQL Optimizer generates execution plan alternatives in an Optimize SQL - Plan Control session, you can test run the alternatives to retrieve run-time statistics and identify the best alternative for your database environment.

SQL Optimizer allows you to test run a single alternative, a selected group of alternatives, or all the alternatives at one time.

To test run execution plan alternatives

  1. After generating execution plan alternatives in a Plan Control session (see Generate Execution Plan Alternatives), use one of the following methods to test run the plan alternatives.

    • To test run a single plan alternative, select the alternative and click .
    • To test run a single plan alternative and specify test-run criteria, select the alternative. Then click the arrow beside and select Test Run Special - Current.
    • To test run all plan alternatives, click .
    • To test run multiple plan alternatives simultaneously, select the alternatives. Then click the drop-down arrow beside and select Test Run - Selected.
  2. If you selected to test run a single alternative using special test-run criteria, the Test Run Special - Current dialog opens. Specify test-run options to apply to this test run only. See Test Run Special - Current for more information.
  3. If you selected to test run multiple or all alternatives simultaneously, 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.
  1. After the test-run process finishes, you can review and compare plan alternatives, and then select an alternative plan to save as a plan baseline. See Deploy Baselines for more information.

Notes:

  • Click to stop the test-run process for multiple SQL.
  • Select a running SQL statement and click to abort only the selected statement.

To clear the Plan Control window

Right-click within the SQL Text pane and do one of the following:

  • Select Clear Optimization Results | Keep Original Scenario to clear only the results and retain both the original SQL statement and the database connection.
  • Select Clear Optimization Results | Clear Original Scenario to clear the Plan Control window, but retain the database connection.

  

Related Topics

Generate Execution Plan Alternatives

Deploy Baselines

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating