Running the SQL Tuning Advisor
Use the SQL Tuning Advisor for tuning SQL statements. Typically, you run this advisor in response to an ADDM performance finding that recommends its use. You can also start the SQL Tuning Advisor manually. One reason is to tune statements that the Automatic SQL Tuning Advisor has not considered for tuning.
As described in "About the SQL Tuning Advisor," the SQL Tuning Advisor can select SQL statements to tune from several sources. The following scenario assumes that you want to tune the SQL statements with the most activity:
To run the SQL Tuning Advisor:
- In the table at the bottom of the Activity tab, select the row that includes the SQL statement that you want to tune, and then click the Tune SQL button.
In this example, the SQL statement in the sixth row of the table is selected.
- The Schedule SQL Tuning Advisor wizard appears, with the Task Information page showing.
On the Task Information page, you can accept the tuning task name generated by the system, or enter a name of your choosing for the tuning task that will be created for the selected SQL statement. You also have the option of entering a description for the tuning task.
Click the right arrow button.
- The Scope page appears. Specify the total time SQL Tuning Advisor should spend analyzing the statement (the default value is Unlimited), and the scope of the analysis (Comprehensive or Limited).
Click the right arrow button.
- The Schedule page appears. On this page, you can schedule SQL Tuning Advisor to analyze the statement immediately or later. If you choose to have SQL Tuning Advisor analyze the statement later, specify the time that you want SQL Tuning Advisor to analyze the statement.
Click OK to begin the tuning task creation by SQL Tuning Advisor for the selected SQL statement.
- When the SQL Tuning Advisor finishes analyzing the SQL statement, the Completed (check mark) icon appears in the Status column on the SQL Tuning Advisor page.
Select the row that includes the SQL statement that SQL Tuning Advisor has finished analyzing, and then click View Result to see the recommendations the SQL Tuning Advisor has for this SQL statement. In this example, the first row is selected.
The Tuning Result for SQL: SQLID page appears, which shows a summary of the tuning recommendations for the selected SQL statement.
- The Select Recommendation section at the bottom of the page shows the recommendations for tuning the SQL statement.
If there are multiple recommendations on the page, only one of them should be implemented.
To implement a recommendation, select it in the table and click Implement. You will be prompted to provide the necessary information to implement the recommendation.
To help you decide which (if any) of the recommendations to implement, you may want to view more details about each of the recommendations.
To view more details about a recommendation, select it in the table, then click View Details.
In this example, the Some alternative execution plans for this statement were found by searching the system's real-time and historical performance data recommendation is selected.
The Recommendation Details page appears.
- The top section of this page describes the performance recommendation, and the section is named after the type of recommendation. Some possible names for this section are "Alternative Plan(s)," "Stale or Missing Statistics," "Restructure SQL," and "SQL Profile." This section provides an overview of the recommendation.
The Compare Explain Plans section at the bottom of the page includes one or more tabs that that enable you to view one or more execution plans for the selected statement. The four tabs that can appear are the Original Plan, Original Plan with Adjusted Cost, Plan Using SQL Profile, and Alternative Plan tabs. The Tabular and Graphical button enable you to display an execution plan in tabular or graphical format. In this example, the execution plan is displayed in tabular format.
For recommendations that do not include a potentially better execution plan, only the Original Plan tab appears, and the operations for the original plan are shown on the Original Plan subpage.
On the Original Plan with Adjusted Cost subpage, the execution plan steps are the same as the Original Plan steps, but the Original Plan with Adjusted Cost steps have different costs for the steps (as shown in the Operation Cost column).
On the Plan Using SQL Profile subpage, the steps are different than the Original Plan steps, and the steps have different costs (as shown in the Operation Cost column).
The Alternative Plan subpage is available when the execution history for the original plan cannot be found. In this case, if you know that the alternative plan suggested by SQL Tuning Advisor is better than the original plan, you can click the Create SQL Plan Baseline button to create a SQL plan baseline for the alternative plan so that the Oracle optimizer will pick the alternative plan for the statement in the future.
To implement a recommendation, click the Back button in your browser, and implement the recommendation on the Tuning Result page.
