SQL Optimizer for Oracle 10.0 - User Guide

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.

DECLARE
   NUM NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO NUM
     FROM GRADE
    WHERE GRD_ID < 1500; 
END;

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;

DECLARE
BEGIN
   INSERT INTO MYTABLE1
      SELECT COUNT (*)
        FROM GRADE
       WHERE GRD_ID < 1500;
END;

DECLARE
   CURSOR C1
   IS
      SELECT GRD_ID
        FROM GRADE
       WHERE GRD_ID < 1500;
BEGIN
   OPEN C1;
   CLOSE C1;
END;

DECLARE
   C1   SYS_REFCURSOR;
BEGIN
   OPEN C1 FOR
      SELECT GRD_ID
        FROM GRADE
       WHERE GRD_ID < 1500;

   CLOSE C1;
END;

DECLARE
   C1   SYS_REFCURSOR;
BEGIN
   OPEN C1 FOR 'SELECT GRD_ID
                  FROM GRADE
                 WHERE GRD_ID < 1500';

   CLOSE C1;
END;

DECLARE
BEGIN
   EXECUTE IMMEDIATE ('SELECT GRD_ID
                         FROM GRADE
                        WHERE GRD_ID < 1500');
END;

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