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
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:
Copies the execution plan to the clipboard.
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.|
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.
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.
SQL Optimizer provides a variety of formats for displaying an execution plan. After selecting a plan format, you can customize the plan display and the Execution Plan window.
To select a plan format
Select a SQL statement.
Select one of the following formats:
To use the Tree Plan
Right-click an execution plan and select View Plan | As Tree Plan.
Select a layout style for the tree plan from the Execution Plan toolbar (right panel). Each style displays different plan details (operators, predicates, statistics).
To select which items/columns to display in the execution plan, right-click the plan and select Plan Options. The settings will apply to the current module only.
In the Actual Plan tab, to display a comparison of Oracle estimates and actual statistics, click the Plan Comparison button in the Execution Plan toolbar.
To use DBMS_XPlan
To select a display level for DBMS_XPlan (Formatted), click the arrow beside the Switch DBMS_XPlan level button and select an option.
To use the Plain Language Plan
Right-click an execution plan and select View Plan | As Plain Language Plan.
To use Graphic Plan
Right-click an execution plan and select View Plan | As Graphic Plan.
To use MS Graphic Plan
Right-click an execution plan and select View Plan | As MS Graphic Plan.
To return to previous view
To return to your previous view, click the Switch View Plan button at the bottom of the Execution Plan toolbar. Use this button to easily toggle between two views. Click the arrow to select a different view.
To customize font style and color
To display step description in footer
To display additional plan information