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 |
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
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 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.
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
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.
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.
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
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.
About Optimizing SQL (SQL Rewrite)
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 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. |
© ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center