Database Reference
In-Depth Information
Figure 11-4.
Main steps carried out during the generation of a SQL profile
Here are the steps in detail:
1.
The user passes the poorly performing SQL statement to the SQL Tuning Advisor.
2.
The SQL Tuning Advisor asks the Automatic Tuning Optimizer to give advice aimed at
optimizing the SQL statement.
3.
The query optimizer gets the system statistics, the object statistics related to the objects
referenced by the SQL statement, and the initialization parameters that set up the
execution environment.
4.
The SQL statement is analyzed. During this phase, the Automatic Tuning Optimizer
performs its analysis and partially executes the SQL statement to confirm its guesses.
5.
The Automatic Tuning Optimizer returns the SQL profile to the SQL Tuning Advisor.
6.
The user accepts the SQL profile.
7.
The SQL profile is stored in the data dictionary.
Figure
11-5
illustrates the steps carried out during the utilization of a SQL profile. The important thing is that the
utilization is completely transparent to the user.
