For SQL statements that contain a bind variable, you can use the Test Run Different Bind Values tab in Optimize SQL to test run SQL alternatives against a number of different bind values. This feature allows you to evaluate how one alternative performs against different bind values. In addition, you can compare the performance of different alternatives across a whole range of bind values within one test run.
You can manually enter bind values or import values from a .txt or .csv file. You can also find and use bind values captured by Oracle. If you import multiple values from a file, you can then select all or a subset of those values to test run. This allows you to easily reuse the same source file of bind values.
To use the Test Run Different Bind Values feature, your SQL must be valid and must contain at least one bind variable.
To test run SQL alternatives with different bind values
Select the Optimize SQL tab in the main window.
In the Finish Setup page, select one of the following options:
Test run only the newly added or modified |
(Applicable only when modifying a current test run) Select this option to test run only the alternatives you added and/or the bind value sets you added or modified. |
Test run all SQL using every set of bind values OR Test run everything again |
Select this option if you want to immediately test run all the SQL alternatives you selected using all the bind values you specified. |
Let me select what to test run | Select this option if you want to select which SQL alternatives to test run or which bind values to use. |
If you selected to test run immediately, the Test Run Settings dialog opens. Specify test-run options for this test run and click Start Test Run. See Test Run Settings for more information.
SQL Optimizer runs the SQL alternatives you selected using each bind value set you defined.
If you selected Let me select what to test run, a page opens allowing you to select which SQL alternatives to test run and which bind values to use.
Make your selections and then click Finish Setup.
If you selected to test run multiple alternatives, the Test Run Settings dialog opens. Specify test-run options for this test run and click Start Test Run. See Test Run Settings for more information.
To review test-run results
Summary pane. When the test-run process is finished, the Summary pane displays a summary of results for the selected alternatives you tested.
Review the Performance for Bind Sets column—The graph in this column displays the relative performance within the group of bind values. Use this chart to observe variation in SQL performance when different bind values are used. Then compare the differences in the amount of variation for different alternatives.
Click the Average Elapsed Time link to change the statistic used to draw the graph. (Changing the performance statistic in this column changes it for all grids and the performance charts.)
If a SQL was terminated, the Number of incomplete test runs is represented by a red bar below the x axis. The number represents the bind value sets that were not test run.
To show more or all terminated test runs, in the field directly above the performance chart, click the link to increase the number of terminated test runs to display.
The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.
In the step formats (like Tree Plan) each step indicates how SQL Optimizer retrieves rows of data. The steps are numbered in the order of execution to make the plan easier to read.
Oracle executes each child operation before the parent operation. For some SQL statements, Oracle executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that Oracle retrieve all rows from the child operation before it executes the parent operation.
Use the Execution Plan window to review the execution plan for the original SQL statement or selected alternative. Several display options and configurations are available.
You can select an execution plan format, configure plan content, and customize plan text.
To select a plan format
The Execution Plan window displays the available execution plans. Each tab contains a different type of execution plan.
Actual Plan—The Actual Plan is retrieved from the plan cache after execution and is the actual execution plan performed by the database to execute the statement.
Right-click in the Execution Plan window to select from the following actions:
Action |
Description |
---|---|
Copies the execution plan to the clipboard. | |
Save |
Saves the execution plan as a .jpg file. The DBMS_XPlan formats can be saved as a .txt file also. |
|
Opens the print window so you can print the execution plan. |
Allows you to change how the execution plan is displayed. | |
Step Description | Select this option to display a description of the selected step in the Execution Plan pane footer. |
Plan Information |
If you select this option, SQL Optimizer identifies whether an execution plan uses any of the following: cardinality feedback, dynamic sampling, SQL plan directives, or adaptive plan. If applicable, the information displays in the Execution Plan pane directly above the Execution Plan tab. |
Highlights, one-by-one, the execution plan steps. | |
Plan Options |
Opens the Execution Plan Options window so you can select which information is displayed in the execution plan and whether to display specific items as a column. |
Help on plan step |
Displays the help text for the currently selected step in the execution plan. |
Help on Execution Plan |
Opens online help for the Execution Plan window. |
Available execution plans for a selected SQL statement are displayed in the Execution Plan window. In Optimize SQL, if a plan is not displayed, use one of the following methods to retrieve it.
To retrieve the Default Plan
After entering or importing SQL in an Optimize SQL session, click . SQL Optimizer retrieves the default plan.
Note: When you generate virtual index alternatives, the execution plan is not retrieved for the SQL statement because SQL Optimizer does not physically create indexes on the database. See Fill Missing Execution Plans for more information.
To retrieve the Actual Plan
Tip: Click the arrow beside and select Refresh All Plans to refresh the plans that have already been retrieved.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center