Viewing Automatic SQL Tuning Results

You can track the activities of the Automatic SQL Tuning Advisor with EM Express.

To view automatic SQL tuning results:

In EM Express, from the Performance menu, choose SQL Tuning Advisor.

The SQL Tuning Advisor page appears, with the Automatic tab showing.

The SQL tuning task that appears on the Automatic tab is the SYS_AUTO_SQL_TUNING_TASK. This tuning task is created daily by the Automatic SQL Tuning Advisor. The task includes any high-load SQL queries for which the Automatic SQL Tuning Advisor has generated tuning recommendations.

Description of the illustration auto_sqlt_advisor.gif

If you configured Automatic SQL Tuning Advisor to automatically implement SQL profile recommendations, then the SQL Profile Potential DB Time Benefit chart on the Automatic tab of the SQL Tuning Advisor page will include an Implemented bar. Click the Implemented bar to see all the SQL profiles that were automatically implemented.
In the Top SQL Statements table, select a row that includes a SQL statement for which you want to view tuning recommendations, and then click View Details.

In this example, the SELECT statement with a SQL ID of ffy1dpzphwuud was selected and View Details was clicked.

The Tuning Result for SQL page appears, which shows a summary of the tuning recommendations for the selected SQL statement.

Description of the illustration sqlta_auto_start.gif
The Select Recommendation section advises that only one recommendation on the page 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 SQL Profile 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 "Stale or Missing Statistics," "Restructure SQL," and "SQL Profile." This section provides an overview of the recommendation.

Description of the illustration sqlta_auto_1_stmt_recomd.gif

The Compare Explain Plans section at the bottom of the page includes 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 Graphical and Tabular buttons enable you to display an execution plan in graphical or tabular 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 tab.

When you click the Original Plan with Adjusted Cost tab, 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).

If you click the Plan Using SQL Profile tab, 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 button appears 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 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.

Click the Implement button at the top of the Recommendation Details page to implement a recommendation.