Tuning SQL Manually Using SQL Tuning Advisor

As described in Identifying High-Load SQL Statements, Automatic Database Diagnostic Monitor (ADDM) automatically identifies high-load SQL statements. If ADDM identifies such statements, then click Schedule/Run SQL Tuning Advisor on the Recommendation Detail page to run SQL Tuning Advisor.

To tune SQL statements manually using SQL Tuning Advisor: 

1. On the Database Home page, under Related Links, click Advisor Central.

   The Advisor Central page appears.

2. Under Advisors, click SQL Advisors.

   The SQL Advisors page appears.

3. Under SQL Tuning Advisor, click SQL Tuning Advisor.

   The Schedule SQL Tuning Advisor page appears.

4. In the Name field, enter a name for the SQL tuning task.

   If unspecified, then SQL Tuning Advisor uses a system-generated name.

5. Do one of the following:

   • To run a SQL tuning task for one or more high-load SQL statements, under SQL Tuning Advisor Data Source Links, click Top Activity.

     The Top Activity page appears.

     Under Top SQL, select the SQL statement you want to tune and click Schedule SQL Tuning Advisor. See "Identifying High-Load SQL Statements Using Top SQL" to learn how to identify high-load SQL statements using the Top Activity page.

   • To run a SQL tuning task for historical SQL statements from the Automatic Workload Repository (AWR), under SQL Tuning Advisor Data Source Links, click Historical SQL (AWR).

     The Historical SQL (AWR) page appears.

     Under Historical SQL (AWR), click the band below the chart, and select the 24-hour interval for which you want to view SQL statements that ran on the database. Under Detail for Selected 24 Hour Interval, select the SQL statement you want to tune, and click Schedule SQL Tuning Advisor.

   • To run a SQL tuning task for a SQL Tuning Set, click SQL Tuning Sets.

     The SQL Tuning Sets page appears.

     Select the SQL Tuning Set that contains the SQL statements you want to tune and then click Schedule SQL Tuning Advisor. See "Creating a SQL Tuning Set" to learn how to create SQL Tuning Sets.

   The Schedule SQL Tuning Advisor page reappears.

6. To display the SQL text of the selected statement, expand SQL Statements.

7. Under Scope, select the scope of tuning to perform. Do one of the following:

   • Select Limited.

     A limited scope takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile.

   • Select Comprehensive, and then set a time limit (in minutes) for each SQL statement in the Time Limit per Statement field, and a total time limit (in minutes) in the Total Time Limit field. Note that setting the time limit too small may affect the quality of the recommendations.

     Comprehensive mode may take several minutes to tune a single SQL statement. This mode is both time and resource intensive because each query must be hard-parsed. Thus, you should only use comprehensive scope for high-load SQL statements that have a significant impact on the entire system.

   See "Managing SQL Profiles" to learn more about SQL profiles.

8. Under Schedule, do one of the following:

   • Select Immediately and then click Submit to run the SQL tuning task immediately.

     The Processing: SQL Tuning Advisor Task page appears.

   • Select Later to schedule a specific time in the future, and then click OK.

9. On the Database Home page, under Related Links, click Advisor Central.

   The Advisor Central page appears.

   Under Advisor Tasks, the Results sections lists the result of advisors.

10. Select a result from the table and then click View Result.

    The Recommendations for SQL ID page appears.

    If you used a SQL Tuning Set, then multiple recommendations may be shown. To help you decide whether to implement a recommendation, an estimated benefit of implementing the recommendation is displayed in the Benefit (%) column. The Rationale column displays an explanation of why the recommendation is made.

11. To implement the recommendation, do one of the following:

    • If an automated solution is recommended, then click Implement.

      A confirmation page appears. Click Yes to confirm the change.

    • If a manual solution is recommended, then consider implementing the recommendation.