Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository
or that you have
specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations,
which could include updating statistics, adding indexes, or creating a SQL profile. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to fix errant SQL plans
without having to touch the underlying SQL.
The tool is often used along with the SQL Access Advisor since that tool provides advice
on materialized views and indexes. Oracle Database 11g introduces a SQL Advisor tool
that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and
now includes a new Partition Advisor). The Partition Advisor component advises on
how to partition tables, materialized views, and indexes in order to improve SQL
In the next lesson, we will begin by describing how the product profile works.
 Automatic Workload Repository:
The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for the database. The gathered data can be displayed in both reports and views. If you use services with your database, then AWR tracks metrics at the service level.