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 Advisors", 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:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the bottom of the page, under Related Links, click Advisor Central.

3. On the Advisor Central page, click SQL Advisors.

4. On the SQL Advisors page, click SQL Tuning Advisor.

   The Schedule SQL Tuning Advisor page appears.

5. In the Overview section, select the Top Activity data source link.

   The Top Activity page appears. This page has a timeline graph showing the database activity based on the active sessions over the last hour.

   
   Description of the illustration top_activity_new.gif
   
   

6. (Optional) Expand the timeline by selecting Historical from the View Data list at the upper right-hand side of the page.

7. Select an interval to analyze by clicking the bar under the timeline graph.

   The Top SQL and Top Sessions tables show the activity within the selected period.

8. In the Top SQL section, select one or more SQL statements. Make sure the Actions drop-down list shows Schedule SQL Tuning Advisor, and then click Go.

   The Schedule SQL Tuning Advisor page returns, indicating the statements that are to be tuned.

9. Enter a task name and description, select the scope for the analysis (Comprehensive or Limited), and select a start time for the analysis task.

10. Click Submit.

    A Processing SQL Tuning Advisor Task page appears. When the task is complete, the SQL Tuning Result Summary page appears, showing a summary of the SQL Tuning Advisor findings for the SQL statements analyzed.

    To view the recommendations, click Show all results. The recommendations can include one or more of the following:

    • Accept the generated SQL profile.

    • Gather optimizer statistics on objects with stale or no statistics.

    • Rewrite a query for better performance.

    • Create an index to offer alternate, faster access paths to the query optimizer.

11. To view recommendations for a specified SQL statement, select a statement from the list of recommendations, and then click View Recommendations.

    The Recommendations for SQL ID page appears, showing one or more recommendations for the statement.

12. (Optional) Select a recommendation, and then click Implement.

    After you confirm that you want to implement a new profile, you are returned to the Recommendations for SQL ID page, and a confirmation notice is printed across the top of the page.