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. 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 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.

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

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

5. 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.

6. 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.

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

   The Recommendations for SQL ID page appears.

   This page can include recommendations for SQL profiles and indexes. See "Tuning SQL Manually Using SQL Tuning Advisor" to learn how to implement recommendations made by SQL Tuning Advisor.