Use the Compare window to view the SQL text, execution plan, and execution statistics for your original SQL statement and all alternatives SQL Optimizer generates. The Compare window consists of the Alternatives window and the Comparison window. The Alternatives window displays execution statistics and the Comparison window displays SQL statements and execution plans. You can compare your original SQL statement with an alternative SQL Optimizer generates or compare two different alternatives.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To compare SQL statements
Select an alternative in the Alternatives pane to compare the alternative plan with your original execution plan.
Note: The Comparison window displays the original execution plan in both panes by default.
Tip: The alternative you selected is shown under your original SQL statement in the Alternatives window by default. To unfreeze the alternative selected, right-click the alternative and select Unfreeze Comparing Rows
Click to switch the location of the original execution plan and selected alternative in the Comparison pane.
Note: Switch the location of the original SQL statement to compare two alternatives. Once you select the first alternative, click and select the second alternative.
Click the current layout option in the Comparison pane to change the layout for the statements.
Review the following for additional information:
|SQL and Plan (Left-Right)||Displays the SQL text and execution plan for the statements you are comparing side by side.|
|SQL Only (Left-Right)||Displays the SQL text for the statements you are comparing side by side.|
|Plan Only (Left-Right)||Displays the execution plan for the statements you are comparing side by side.|
|SQL and Plan (Top-Bottom)||Displays the SQL text and execution plan for the statements you are comparing beneath each other.|
|SQL Only (Top-Bottom)||Displays the SQL text for the statements you are comparing beneath each other.|
|Plan Only (Top-Bottom)||Displays the execution plan for the statements you are comparing beneath each other.|
Tip: Click to maximize the Comparison pane. You can click to restore the Comparison pane to its original size.
Use Best Practices in Optimize SQL to analyze your SQL statement and database to recommend common techniques for improving database performance. Since the recommendations can also affect performance of other statements in your database, you should review and test the recommendations before implementing them. When evaluating the recommendations, take into account that database performance is affected by the following:
System resources (CPU, I/O, memory, database architecture, and more)
SQL execution plans
User's usage behavior
Best Practices is only available in SQL Rewrite mode in Optimize SQL.
To view best practices
Select a statement in the Alternatives Details pane.
You can generate a resolution report for a SQL Rewrite or Plan Control session after you generate execution plan alternatives. The resolution report includes the following:
Tip: You can also export the contents of a SQL Rewrite grid (or other grid) to an Excel file. Right-click the grid and select Save As | Excel Document. Other file formats are also available.
To generate a report
Use the report window to customize the layout and the information displayed in the report, and to select publishing options. Use the following Report toolbar buttons.
|Current Settings. Click to specify the information to include in your report.|
|Page Setup. Click to modify margins and select page orientation.|
|Scale. Click to adjust the scale of the printed output.|
Watermark. Click to add a watermark to the report.
|Color. Click to change the report background color.|
Export Document. Click to save the report to file using the default format. Click the arrow to select from several file format options (or to change the default setting).
You can select from several different file formats, including PDF, HTML, and Excel.
|Send via Email. Click to save the report to file using the default format and to also attached the file to an email. Click the arrow to select from several file format options (or to change the default setting).|
Note: To select SQL or plan alternatives to include in the report, click and then click Select Alternatives. Select alternatives from the list.
To export the report as a PDF file
The Register SQL Translation feature allows you to substitute a better-performing SQL alternative for your original SQL without modifying the application code. This feature uses Oracle SQL Translation Framework to store the original SQL and the substitution (translation) and then run the substitution in place of the original SQL in the client application. The original/substitution SQL pairs are stored in a SQL Translation Profile.
After optimizing your application SQL, use this feature to deploy (register) a better-performing SQL alternative (SQL translation). Then Oracle runs the SQL alternative in place of the original SQL in the application.
Note: This feature is only applicable to SQL sent from a client application. For SQL originating from a database object (e.g., stored procedure), the SQL substitution/translation process does not take place.
To register a SQL translation
In the Register SQL Translation dialog, select an existing SQL Translation Profile or create a new one to store the SQL translation. A SQL Translation Profile is a database object that contains the original SQL statement and its SQL translation.
You can edit the Original SQL and the Translation SQL text in the editor windows. The modified SQL text is sent to the profile when you click Add.
Note: The format of the Original SQL text must be exactly the same as the SQL in your client application for the SQL substitution to take place.
Note: This feature is only available when connected to Oracle Database 12c or later.
To use a SQL Translation
To use a SQL translation, use the following commands in Oracle:
alter session set events = '10601 trace name context forever, level 32';
alter session set sql_translation_profile = <profile_name>;