Tuning SQL in Query Window

To tune SQL statements in the Query Window, type the SQL directly into the Query text area and click Tune SQL in the tool bar. This causes a Tune SQL tab to appear, showing the findings and recommendations.

A user must be granted the ADVISOR privilege to be able to use SQL Tuning Advisor.

SQL Tuning may take some time to complete and during this time you may cancel through the toolbar or the Cancel Query menu item. You may continue to use Visual Studio, however, you will not be able to tune another SQL statement until the one is complete or cancelled.

Please note that the Cancel operation does not abort the SQL tuning right away, instead it interrupts the process and any recommendations gathered so far will be displayed in the grid.

In the Query Window, you can only tune one SQL statement at a time. If you have multiple SQL statements in Oracle Query Window, the Tune SQL menu item tunes the selected SQL statement or the last statement.

Note:

SQL Tuning Advisor requires an Oracle Tuning Pack License. See About SQL Tuning Advisor.

Description of tuning_advisor.gif follows

Description of the illustration tuning_advisor.gif

Query Window SQL Tuning Advisor Controls

Control	Description
Tune SQL Tab	Lists the recommendations for the action selected. If there are no recommendations to tune the SQL, a message appears indicating this. This is a grid that contains columns for: Type - Recommendation Type. Findings Recommendations Rationale Benefit (%) - An estimate of the percent of `DB` `time` (database time) that can be saved if the recommendation is implemented.
Implement Recommendation	Implements the highlighted action. If the recommendation Type is SQL Profile and there are existing SQL Profiles, you are asked to OK implementing a new SQL profile that overwrites an existing one. Implement Recommendation only appears if you select a Recommendation Type of `STATISTICS` or `SQL` `Profile`. It does not appear for the other recommendation types.
Preview SQL	Displays SQL for the highlighted action. Preview SQL only appears if you select a Recommendation Type of `STATISTICS` or `SQL` `Profile`. It does not appear for the other recommendation types.
View Report	Opens the SQL Tuning Advisor report in a text editor which can then be modified and saved to a text file, if desired.