SQL Optimizer for Oracle 10.0 - User Guide

Test Run Settings

Use the Test Run Settings dialog to specify test-run options and optimization goals when you test run multiple SQL simultaneously. This dialog opens when you initiate the test-run process in Optimize SQL. The options you specify are used for the current test run only.

When you initiate the test-run process from the SQL Details tab, the Test Run Settings dialog provides two methods for specifying test-run settings:

• You can allow SQL Optimizer to determine the best test run settings based on your answers to a few questions.
• Or you can customize the test run settings yourself.

Test Run Different Bind Values

If you initiate the test run process from the Test Run Different Bind Values tab, read Test Run Settings for Multiple Bind Values first.

Allow SQL Optimizer to Determine Settings

If you want SQL Optimizer to choose the best test-run settings for you, simply answer the questions on the first page (Usage and Symptom) of the Test Run Settings dialog. SQL Optimizer will determine your optimization goals based on your answers.

The settings that SQL Optimizer selects are displayed in the Recommended Settings section at the bottom of the Usage and Symptom page.

To allow SQL Optimizer to determine the best test run settings

1. Answer the questions on the first page (Usage and Symptom) of the Test Run Settings dialog.
2. After answering the questions, review the test-run settings selected by SQL Optimizer in the Recommended Settings section (bottom of page).
3. Click Start Test Run.

Customize Test Run Settings

If you want to configure the test-run settings manually, click the Customize Test Run Settings link which opens additional pages in the Test Run Settings dialog.

To customize the test run settings

1. Click the Customize Test Run Settings link located at the bottom of the first page (Usage and Symptom) of the Test Run Settings dialog.

2. Specify custom test-run options on the Customized Setup page. Review the following for additional information:

   Execution Method

   Review the following options.

   Execution Method

   See Select an Execution Method for help determining if SQL is static or dynamic. 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

   Rows Retrieved

   Select one of the following: Retrieve all rows—Select this option to retrieve all rows. Retrieve only this first number of rows from the SQL—Select this option to specify the number of rows to retrieve. Purpose: This option is useful for simulating the performance of SQL which normally retrieves only a specific number of rows. Tip: You can specify a default value in Options | Optimize SQL | Test Run.

   Run Time Retrieval Method

   Review the following options.

   Dynamically determine the number of times to test run (running at most twice)	(Not applicable to the Test Run Different Bind Values feature) If the run time of your original SQL is less than the threshold you specify here, your original SQL and all alternatives run twice. The second run time is used as the test run result. Otherwise, all SQL run once. Tip: You can specify a default value for this threshold in Options | Optimize SQL | Test Run.
   Dynamically determine the number of times to test run (may run multiple times)	(Not applicable to the Test Run Different Bind Values feature) Determine how many times to run SQL if the run time of your original SQL is less than the thresholds you specify here. In the first statement, specify max run time and number of runs. If the run time of your original SQL is less than this threshold, your original SQL and all alternatives run the number of times you specify, and the average run time is used. Tip: You can specify default values for these two editable options in Options | Optimize SQL | Test Run. In the second statement, if the run time of the original SQL is less than the threshold you specify here, the original SQL and all alternatives run twice, and the second run time is used. Otherwise, all SQL run once.
   Run original (or first executed) SQL twice and all other SQL once	Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements.
   Run all SQL twice and measure the second run time	Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. Also, if the SQL statement uses different indexes, one index may be cached while another may not be cached. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison.
   Run all SQL once	Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.
   Flush BUFFER_CACHE before running each SQL (Running SQL once)	Executes all SQL statements once, but flushes the buffer cache prior to running each statement, so each statement starts with a cleared cache. Caution: Setting this option results in flushing the entire buffer pool. All users currently connected to the same database will be affected. We DO NOT recommend using the flush BUFFER_CACHE option on a production database! Flushing the data buffer cache can impose a serious performance overhead (especially on an Oracle RAC database). The Flush BUFFER_CACHE option is intended for use only on a test database. Notes: You must have ALTER SYSTEM privileges to flush the BUFFER_CACHE. This option is only available if you are connected to an Oracle 10g (or later) database.

   Order and Termination

   Review the following options. If testing with different bind values, see Test Run Settings for Multiple Bind Values for exceptions.

   Execution Order

   Select one of the following: Intelligent order—Executes representative SQL statements with various plan costs according to SQL Optimizer's intelligence engine. Plan Cost—Executes SQL statements in order of plan cost.

   SQL Termination Criteria

   Select one of the following: Run time of the fastest SQL - this test run only Finds the fastest run time of the SQL in this test run. Cancels SQL statements that run longer than the current fastest run time. With this option, the first SQL statement runs and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time. Run time of the fastest SQL - including fastest run time from tested alternatives Similar to the previous option, except the fastest run time from tested alternatives is used as the initial termination time for this test run. When a SQL statement runs faster than this time, the faster time is used as the new termination time. This percentage of the original SQL run time—Cancels SQL statements whose total elapsed time is the specified % of the total elapsed time for the original SQL statement. It terminates all SQL statements that run longer than the calculated termination time. If the original SQL has been tested, but is not included in the test run, select whether to use the original SQL's existing run time or to run it again. If the original SQL has not been tested, it will be included in the test run. User-defined time (min:sec)—Cancels SQL statements that run longer than the time you specify here. If the original SQL is included in the test run, specify whether to terminate the original after the specified time or allow original SQL to run until finished. Run without termination—Runs all SQL statements to completion, regardless of run time. Termination delay (seconds)—Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server.

   Best Alternative Criteria

   If testing with different bind values, see Test Run Settings for Multiple Bind Values for exceptions.

   Best SQL Alternative Selection Criteria

   Select one of the following : Elapsed Time—Uses the total elapsed run time to find the best SQL alternative or best index set. First Row Time—Uses the time to retrieve the first record to find the best SQL alternative or best index set. Physical Reads—Uses the number of physical reads to find the best SQL alternative or best index set. Session Logical Reads—Uses the number of logical reads to find the best SQL alternative or best index set. CPU Used by this Session—Uses the execution CPU time to find the best SQL alternative or best index set.

   SQL To Test Run

   (Not available if using the Auto Optimize command or the Test Run Different Bind Values feature)

   Test run criteria

   Select one of the following: % of alternatives with lowest cost—Enter the percentage of SQL alternatives to execute with the lowest Oracle Plan cost. You can also enter the minimum and maximum number of alternatives to execute. Number of alternatives with lowest cost—Enter the number of SQL alternatives to execute with the lowest Oracle Plan cost. All alternatives with cost less than or equal to original SQL—Select to execute all SQL alternatives with an Oracle Plan cost less than or equal to the cost of the original SQL statement. All alternatives with cost less than the average of all alternatives—Select to execute all SQL alternatives with an Oracle Plan cost less than the average cost of all alternatives. All alternatives with cost less than the original SQL by percentage—Enter a percentage used to determine the SQL alternatives selected for execution. SQL Optimizer executes alternatives with an Oracle Plan cost that is the specified percentage lower than the cost of the original statement. All alternatives with cost less than the original SQL by N times—Enter a value for N used to determine the SQL alternatives selected for execution. SQL Optimizer executes alternatives with an Oracle Plan cost N times lower than the original SQL statement. All alternatives—Select to execute all SQL alternatives.

3. After specifying custom options, click Start Test Run.

Tip: You can specify some default options for this dialog in Options | Optimize SQL | Test Run. See Test Run Options for more information.

Test Run Settings for Multiple Bind Values

If you initiate the test-run process from the Test Run Different Bind Values tab, the Test Run Settings dialog automatically opens to the custom settings pages. Review the following exceptions. Then return to Customize Test Run Settings to finish specifying custom test-run options.

• Execution Method: Execution Plan

  When a SQL statement is executed for a second time using different bind values, Oracle might reuse the execution plan from the previous execution (a feature known as Cursor Sharing). To proceed with this, select to “reuse the cached execution plan”. If you want cached execution plans to have no bearing on how Oracle chooses to execute the SQL statement then select to “ignore the cached execution plan”.

  Ignore the cached execution plan	Hide from Oracle any cached execution plans from previous bind value set executions. When you select this option, cached execution plans will have no bearing on how Oracle chooses to execute the SQL statement. This option allows you to measure the performance of alternative SQL statements in each bind value set without potential interference (and in isolation) from previous executions.
  Reuse the cached execution plan	This matches the default Oracle behavior. It better simulates the performance of the alternative SQL statements in a real database environment where Cursor Sharing may occur.

• Order and Termination: SQL Termination Criteria

  When testing multiple bind values, for the following options each SQL is executed using all bind value sets and then the total of all run times is used.

  Note: If you select Let me select what to test run on the Finish Setup page, these options are not available in the Test Run Settings dialog.

  Total run time of the fastest SQL	Finds the total run time of the fastest SQL in this test run. Total run time = Set 1 run time + Set 2 run time, etc. Then terminates a SQL if its cumulative run time exceeds the fastest total run time. If a SQL is terminated, any remaining bind value sets are not executed. Use this option to shorten the test-run process.
  This percentage of original SQL's total run time	Finds the total run time of the original SQL and then applies the percentage you specify. Similar to the preceding option in that if a SQL is terminated, any remaining bind value sets are not run.

  When testing multiple bind values, the following option uses the individual run time of an alternative with one bind value set.

  User-defined time

  Applies to the run time for individual SQL, not the total. Cancels the SQL / bind value sets that run longer than the time you specify. The termination of a SQL / bind value set does not affect execution with the remaining bind value sets.

• Best Alternative Criteria

  Select the performance statistic to use to find the best alternative. When testing multiple bind values, for each alternative, the SQL statement is executed using all bind values and then the average is used.

Related Topics

Optimize SQL Statements

Test Run SQL Alternatives

Test Run Execution Plan Alternatives

Test Run Different Bind Values

Test Run Special Current Settings

Use the Test Run Special Current dialog to specify test-run options when you test run a single SQL or plan alternative. This dialog opens when you select the Test Run Special - Current command to initiate the test-run process. The options you specify are used for the current test run only.

To specify Test Run options for a single alternative

1. When the Test Run Special Settings dialog opens, specify test-run options. Review the following for additional information:

   Execution Method	See Select an Execution Method for help determining if SQL is static or dynamic. 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
   Rows to Retrieve	Select one of the following: Retrieve all rows—Select this option to retrieve all rows. Retrieve only this first number of rows from the SQL—Select this option to specify the number of rows to retrieve. Purpose: This option is useful for simulating the performance of SQL which normally retrieves only a specific number of rows. Tip: You can specify a default value in Options | Optimize SQL | Test Run.
   Run Time Retrieval Method	Select one of the following: Run the SQL once—Runs the SQL one time. Run the SQL multiple times—Select this option to run the SQL alternative multiple times. Then specify the number of runs. Flush BUFFER_CACHE before running each SQL (Running SQL once)— Executes all SQL statements once, but flushes the buffer cache prior to running each statement, so each statement starts with a cleared cache. Caution: Setting this option results in flushing the entire buffer pool. All users currently connected to the same database will be affected. We DO NOT recommend using the flush BUFFER_CACHE option on a production database! Flushing the data buffer cache can impose a serious performance overhead (especially on an Oracle RAC database). The Flush BUFFER_CACHE option is intended for use only on a test database. Notes: You must have ALTER SYSTEM privileges to flush the BUFFER_CACHE. This option is only available if you are connected to an Oracle 10g (or later) database.

2. Click Start Test Run.

Related Topics

Test Run SQL Alternatives

Test Run Execution Plan Alternatives

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;

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