Viewing Automatic SQL Tuning Results

By analyzing data in the Automatic Workload Repository (AWR), the database can identify routine maintenance tasks. The automated maintenance tasks infrastructure (known as AutoTask) schedules these tasks to run in maintenance windows.

Maintenance windows are Oracle Scheduler time intervals that belong to the window group named MAINTENANCE_WINDOW_GROUP. By default, one window is scheduled for each day of the week. You can customize attributes of these maintenance windows, including start and end times, frequency, and days of the week.

By default, AutoTask runs the following automated maintenance tasks in all maintenance windows:

• Optimizer Statistics Collection

• Segment Advisor

• SQL Tuning Advisor

You can view the results of automated execution of SQL Tuning Advisor on observed high-load SQL statements.

To view automatic SQL tuning results:

1. Access the Database Home page.

   See "Accessing the Database Home Page" for more information.

2. From the Performance page, select Advisors Home.

   If the Database Login page appears, then log in as a user with administrator privileges. The Advisor Central page appears.

3. Under Advisors, click SQL Advisors.

   The SQL Advisors page appears.

4. Under SQL Tuning Advisor, click Automatic SQL Tuning Results.

   The Automatic SQL Tuning Result Summary page appears.

   The top half of the page includes sections for the status and activity summary of the SQL Tuning task.

   
   Description of the illustration sql_tuning_auto_result.gif

5. In the Time Period list, select All and then click Go.

   The Overall Task Statistics and Profile Effect Statistics sections are refreshed.

   
   Description of the illustration sql_tuning_auto_stats.gif

6. Optionally, in the Task Status section, click Configure to change the attributes of the Automatic SQL Tuning task.

   The Automated Maintenance Tasks Configuration page appears.

   In this page, you can enable or disable the Automatic SQL Tuning task and specify which days it should run. Click Apply or Revert to return to the previous page.

7. In the Task Activity Summary section, leave All selected for the Time Period and then click View Report.

   The Automatic SQL Tuning Result Details page appears.

   The page lists SQL statements that have been automatically selected by the database as candidates for SQL tuning.

   
   Description of the illustration sql_tuning_result_details.gif

8. Under Recommendations, select a SQL statement and then click View Recommendations.

   The Recommendations for SQL ID page appears.

   
   Description of the illustration sql_tuning_recommend.gif

   This page can include recommendations for SQL profiles and indexes.

   See Also:

   "Tuning SQL Manually Using SQL Tuning Advisor" to learn how to implement recommendations made by SQL Tuning Advisor