Automatic SQL Tuning

As you know, during execution of a SQL statement the Optimizer generates several execution plans, estimates their cost, and selects the one it considers to be the most optimal. This is all done as part of the parse phase of SQL statement processing, and it is performed within a confined and limited period of time. This is done so that the parse and optimization of the statement is not so lengthy as to interfere with the execution of the statement itself. This is known as the Optimizer normal mode.

What Is Enhanced Tuning Mode?

However, the Optimizer can operate in an enhanced tuning mode using what is known as the Automatic Tuning Optimizer (ATO). Rather than seconds, minutes of time are allocated for this enhanced Optimizer to perform its work and select a far better plan than might be chosen in normal mode. The analysis of the ATO includes these steps: