SQL Optimizer for Oracle 10.0 - User Guide

Auto Optimize SQL Statements

Use the Auto Optimize function to run the optimization and testing processes simultaneously. The function optimizes your original SQL statement by generating alternatives and starts testing once SQL Optimizer generates the first alternative. The Auto Optimize function reduces optimization time by not waiting until SQL Optimizer generates all alternatives before starting the testing process. You can stop the Auto Optimize function once you find a satisfactory SQL statement alternative.

To automatically optimize a SQL statement

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

4. Click .

5. 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.
6. When the optimization and testing processes are finished, the alternatives and run-time statistics display in the Alternatives pane. Select an alternative to see more information in the SQL Text and Execution Plan panes.

Tips:

• Click to stop the optimization and testing process. See Stop and Resume the Optimization Process for more information.
• If the SQL contains a bind variable, the Setting Bind Variables dialog allows you to enter a value. See Set Bind Variables for more information.

• If you sent your original SQL statement to SQL Optimizer from another Toad application, click to send the selected alternative SQL back to the original application.

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)

Optimize SQL Statements

Test Run SQL Alternatives

After SQL Optimizer rewrites your original SQL statement, you can test run the SQL alternatives to retrieve execution times and other run-time statistics. You can test run a single statement, a selected group of statements, or all the statements at one time.

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.

To learn how to generate SQL alternatives in a SQL Rewrite session, see Optimize SQL Statements.

To test run all alternatives

1. After generating SQL alternatives in a SQL Rewrite session, click the arrow beside and select Test Run - All.
2. The Test Run Settings dialog opens. Specify test-run options to apply to this test run only. See Test Run Settings for more information about selecting test-run options.

To test run multiple alternatives

1. After generating SQL alternatives in a SQL Rewrite session, use Ctrl+Click to select multiple SQL statements in the Alternatives pane.
2. Click the arrow beside and select Test Run - Selected.
3. The Test Run Settings dialog opens. Specify test run options to apply to this test run only. See Test Run Settings for more information about selecting test-run options.

To test run non-tested alternatives

1. If you stopped (interrupted) the test-run process and want to resume testing the remaining untested 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.
2. The Test Run Settings dialog opens. Specify test run options to apply to this test run only. See Test Run Settings for more information about selecting test-run options.

To test run a single alternative

1. After generating SQL alternatives in a SQL Rewrite session, select a SQL statement or index alternative in the Alternatives pane.
   • To test run the selected alternative, click .
   • To specify test-run criteria, click the arrow beside and select Test Run Special - Current.
2. If you selected to test run a single alternative using special test-run criteria, the Test Run Special - Current dialog opens. Specify criteria for this test run only. See Test Run Special - Current for more information.
3. After the test-run process is finished, the execution statistics display in the Alternatives pane. Select the alternative to view details in the Alternative Details pane and the Execution Plan pane.

Tips:

• Click to stop (or interrupt) the test-run process for multiple SQL. See Stop and Resume the Optimization Process for more information.
• Select a running SQL statement and click to abort only the selected statement.
• To specify default values for some Test Run Settings options, go to Options | Optimize SQL | Test Run.

• If you sent your original SQL statement to SQL Optimizer from another Toad application, click to send the selected alternative SQL back to the original application.

Test Run Multiple Times

To obtain statistics for statements with run times in the millisecond range, you can instruct SQL Optimizer to execute each statement multiple times to obtain the average run-time statistics.

Statements with run times in the millisecond range can be skewed by other active processes because run time is based on a CPU's clock time. Multiple active processes can slow down CPU performance and result in a longer than normal run time for a statement.

When SQL is run twice, the second run time is used. When SQL runs multiple times, the statistics reported are an average of the results from the multiple executions.

To test run a single alternative multiple times

1. After generating SQL alternatives, select a statement in the Alternatives pane.
2. Click the arrow beside and select Test Run Special - Current. The Test Run Special - Current dialog opens.
3. In the Run Time Retrieval Method section, specify the number of times to test run the SQL. See Test Run Special - Current for more information.

To test run selected or all alternatives multiple times

1. Select multiple statements and select Test Run - Selected, or select Test Run - All to test run all statements.
2. In the Test Run Settings dialog that opens, click the Custom Test Run Settings link at the bottom of the page.
3. Select the Run Time Retrieval Method tab.
4. Use this tab of the Test Run settings dialog to specify the number of times to test run the SQL. See Test Run Settings for more information.

Related Topics

Optimize SQL Statements

Automatically Optimize SQL Statements

Set Bind Variables

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