13-9. SQL Tuning Advisor

Oracle's Tuning Advisor calls Automatic Tuning Optimizer (ATO) to perform 4 types of analysis:

• Statistics Analysis: The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and makes recommendations to gather relevant statistics.
• SQL Profiling (Tune SQL plan): The ATO verifies its own estimates and collects auxiliary information to remove estimation errors. It builds a SQL profile using the auxiliary information and makes a recommendation to create it. When a SQL profile is created, it enables the query optimizer to generate a well-tuned plan.
• Access path Analysis: the ATO explores whether a new index can be used to significantly improve access to each table in the query and, when appropriate, makes recommendations to create such indexes.
• SQL Structure Analysis: The ATO tries to identify SQL statements that use bad plans and makes relevant suggestions to restructure them. The suggested changes can be syntactic as well as semantic.

It's important to choose the appropriate scope for the tuning task. If you choose the Limited option, then recommendations are based on:

• Statistics check
• Access path analysis
• SQL structure analysis

If you choose comprehensive option, then SQL profile recommendations are generated as well.