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:
If you initiate the test run process from the Test Run Different Bind Values tab, read Test Run Settings for Multiple Bind Values first.
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
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
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 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.
|
Rows Retrieved |
Select one of the following:
|
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. Notes:
|
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:
|
SQL Termination Criteria |
Select one of the following:
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 :
|
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:
|
Tip: You can specify some default options for this dialog in Options | Optimize SQL | Test Run. See Test Run Options for more information.
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.
Test Run Execution Plan Alternatives
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
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 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.
|
Rows to Retrieve |
Select one of the following:
|
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. Notes:
|
Test Run Execution Plan Alternatives
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.
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 |
|
Static SQL |
Cursor FOR LOOP Statement |
|
Static SQL |
DML Statement |
|
Static SQL |
Explicit Cursor |
|
Static SQL |
Cursor variable with unquoted select statement |
|
Static SQL |
Cursor variable with quoted select statement in string literal, variable, or expression |
|
Dynamic SQL |
EXECUTE IMMEDIATE Statement |
|
Dynamic SQL |
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
Select the Optimize SQL tab in the main window.
Enter a SQL statement with a bind variable.
Click .
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 |
Variable Value |
Enter a value for the variable. Leave the field blank to specify NULL. |
Data Browser | |
SELECT |
Click |
FROM |
Click Tip: Click |
WHERE |
Enter a WHERE clause or click |
ORDER BY |
Enter an ORDER BY clause or click |
Auto Fill |
Click the Auto Fill button to find the last bind values captured by Oracle. |
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center