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

Select an Execution Method

In the Test Run Settings dialog (or Options dialog), SQL Optimizer allows you to choose the best execution method for measuring the performance of your SQL statements in a test run. Select an execution method based on how the SQL is normally used.

If specifying How this SQL is used on the Usage and Symptom page (Test Run Settings dialog), use the following information to help select an option. See Test Run Settings for more information about the Test Run Settings dialog.

Select an execution method:

Run on server—Select one of the following methods to execute on the server as either static or dynamic SQL. The SQL is executed on the server without returning the data to the client. The run time statistics provided when you select these options only include CPU time.

  • Run on server - SQL will be executed inside a PL/SQL block as static SQL

  • Run on server - SQL will be executed inside a PL/SQL block as dynamic SQL

    Note: You must have SYS.DBMS_SQL package privileges to retrieve run times from the server when using these options.

Run on client—Select the following method to test run on the client. Executes SQL statements and returns the data to the client. The run time statistics provided when you select this option include CPU time and data transfer time between the server and client.

  • Run on client - SQL will be executed as dynamic SQL

Determine if SQL is Static or Dynamic

Use the following examples to help determine how your SQL statements are normally executed: as static or dynamic SQL.

PL/SQL Description PL/SQL Example Static/Dynamic SQL
SELECT INTO Statement 
DECLARE
   NUM NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO NUM
     FROM GRADE
    WHERE GRD_ID < 1500; 
END;
 Static SQL
Cursor FOR LOOP Statement 
DECLARE
   TEMP   VARCHAR (100);
BEGIN
   FOR REC IN (SELECT GRD_ID
                 FROM GRADE
                WHERE GRD_ID < 1500)
   LOOP
      TEMP := REC.GRD_ID;
   END LOOP; 
END;
 Static SQL
DML Statement 
DECLARE
BEGIN
   INSERT INTO MYTABLE1
      SELECT COUNT (*)
        FROM GRADE
       WHERE GRD_ID < 1500;
END;
Static SQL
Explicit Cursor 
DECLARE
   CURSOR C1
   IS
      SELECT GRD_ID
        FROM GRADE
       WHERE GRD_ID < 1500;
BEGIN
   OPEN C1;
   CLOSE C1;
END;
Static SQL
Cursor variable with unquoted select statement 
DECLARE
   C1   SYS_REFCURSOR;
BEGIN
   OPEN C1 FOR
      SELECT GRD_ID
        FROM GRADE
       WHERE GRD_ID < 1500;

   CLOSE C1;
END;
Static SQL
Cursor variable with quoted select statement in string literal, variable, or expression 
DECLARE
   C1   SYS_REFCURSOR;
BEGIN
   OPEN C1 FOR 'SELECT GRD_ID
                  FROM GRADE
                 WHERE GRD_ID < 1500';

   CLOSE C1;
END;
Dynamic SQL
EXECUTE IMMEDIATE Statement 
DECLARE
BEGIN
   EXECUTE IMMEDIATE ('SELECT GRD_ID
                         FROM GRADE
                        WHERE GRD_ID < 1500');
END;
Dynamic SQL

 

  

Stop and Resume Optimization Process

For SQL Rewrite sessions, SQL Optimizer allows you to stop the optimization process and resume it at a later time without losing the SQL alternatives and test run results generated thus far in your session.

This is useful if you need to close SQL Optimizer or shut down your computer in the middle of a long-running optimization session. When it is convenient, reopen the saved session and resume running the optimization process beginning from the point where it was interrupted.

This is also useful if you need to stop the process to modify the optimization settings to search for more alternatives if the current intelligence level proves to be insufficient.

To stop and resume the optimization process

  1. During a SQL Rewrite session, click to stop the process.
  2. After the optimization process stops, SQL Optimizer displays process status, results, and next steps in the Alternatives pane to help you determine where the process was interrupted and what action to take next.
    • If the optimization process was interrupted, a warning that the process was stopped displays .
    • If the test run process was interrupted, alternatives that were in the process of executing display a status of Stopped in the Alternatives pane.
  3. If you want to modify the intelligence level for this session to search for more alternatives, click or and then resume the process.
  4. If you want to resume the process at a later time, save the session and close it.
  5. To resume the optimization process, open the saved session and do one of the following based on the process you initiated and where it was interrupted:
    • To continue running the Auto Optimize process, click . If the Auto Optimize process did not find all alternatives, SQL Optimizer continues to search for alternatives.
    • To resume the Rewrite process, click to continue to find alternatives. If all alternatives have already been found, SQL Optimizer informs you.

    • To continue to test run alternatives, click the drop-down arrow beside and select Test Run - Non-Tested. This command tests the remaining untested alternatives. Alternatives with a status of Stopped (stopped by the user) are test run. Alternatives with a status of Aborted, Terminated, or Error are excluded from execution.

      Note: If you select Test Run - All, SQL Optimizer executes untested alternatives and also re-executes tested alternatives.

      For more information about Test Run commands, see Test Run SQL Alternatives.

    • To continue to test run alternatives using different bind values, select the Test Run Different Bind Values tab. Then click the drop-down arrow beside and select Test Run - Non-Tested. Alternatives with a status of Stopped (stopped by the user) are test run. Alternatives with a status of Aborted, Terminated, or Error are excluded from execution.

      Note: If you select Test Run - All, SQL Optimizer executes untested alternatives and also re-executes tested alternatives.

    • To continue to find index alternatives for your original SQL, click the arrow beside and select Index.

    • To continue to test run index alternatives, click the drop-down arrow beside and select Test Run - Non-Tested. Alternatives with a status of Stopped (stopped by the user) are test run. Alternatives with a status of Aborted, Terminated, or Error are excluded from execution.

    • Note: If you select Test Run - All, SQL Optimizer executes untested alternatives and also re-executes tested alternatives.

  

Related Topics

Optimize SQL Statements

Create User Alternatives

In addition to using SQL Rewrite sessions in Optimize SQL to generate SQL statement alternatives, you can create user-defined alternatives. You can create user-defined alternatives using SQL text from your original SQL statement or from SQL text of an alternative SQL Optimizer generated. Once you create a user-defined alternative, you can create virtual indexes for the alternative to test performance.

Create User Alternative from an existing alternative

  1. After generating SQL alternatives in a SQL Rewrite session, choose one of the alternatives to use as a starting point for creating a user-defined alternative (or use the original SQL).

  2. Select the chosen SQL alternative in the Alternatives list and click .

    Note: Optionally, you can right-click an alternative in the Alternatives list and select User Alternative.

  3. Your new User Alternative is added to the Alternatives list and the text of the alternative you chose is added to a new SQL Text tab in the Alternatives Details pane. Rewrite the text of the alternative to create your user-defined alternative.
  4. Click to retrieve the execution plan. If the execution plan is the same as the original SQL or one of the alternatives, the message This scenario has the same plan as <scenario name> displays.
  5. To create an index for the user-defined alternative, select the Virtual Indexes tab and click to add an index.
  6. Build the index by selecting columns and other options.

  7. In the Virtual Plan tab of the Execution Plan pane, click Virtual Explain Plan to retrieve the execution plan.

    Note: If the index you create is not used, displays next to the index name in the Virtual Indexes pane.

  8. Select the Index Script tab to view the SQL script for your virtual indexes.

    Note: The Index Script tab only displays after you create virtual indexes.

Create User Alternative in a new SQL Rewrite session

  1. Select Optimize SQL in the main menu.
  2. Select SQL Rewrite from the Optimize SQL start page.
  3. Enter a SQL statement in the Alternative Details pane.

  4. Click . Select a connection.

    Your new User Alternative is added to the Alternatives list and the SQL text is added to a new SQL Text tab in the Alternatives Details pane. Notice that your original statement is also retained.

  5. You can further edit the statement in the SQL Text tab, if necessary.
  6. Create indexes using the instructions in the section above.

 

Related Topic

About Optimizing SQL (SQL Rewrite)

Set Bind Variables

You need to define the values of SQL statement variables before you optimize the statement. The Set Bind Variables window displays automatically when you use Optimize SQL for a statement with variables.

If you are using Optimize SQL and you want to test run SQL alternatives using multiple bind values, see Test Run Different Bind Values.

Troubleshooting: If the Set Bind Variables window displays when you optimize a statement without variables, make sure you spelled the column and table names correctly, you selected the correct database or user, and you selected a table or column that exists in the database.

To set a bind variable

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

  2. Select a SQL Rewrite or Plan Control Session.

  3. Enter a SQL statement with a bind variable.

  4. Click .

  5. Specify a bind value and data type in the Setting Bind Variables dialog. To browse data for a value, use the Data Browser pane. To find values captured by Oracle, click Auto Fill. Review the following for additional information:

    Bind Variables Form  

    Datatype

    Click and select the variable datatype.

    Variable Value

    Enter a value for the variable. Leave the field blank to specify NULL.

    Data Browser  

    SELECT

    Click and select column references for the variables.

    FROM

    Click and select table references for the variables.

    Tip: Click to browse to tables in a different database.

    WHERE

    		 Enter a WHERE clause or click to select a previously entered clause.

    ORDER BY

    		 Enter an ORDER BY clause or click to select a previously entered clause.
    Auto Fill

    Click the Auto Fill button to find the last bind values captured by Oracle.

 

Related Topic

Automatically Optimize SQL Statements

Generate Execution Plan Alternatives

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating