Database Reference
In-Depth Information
Two SQL statements with the same text have the same signature. That is also true even if they reference objects
in different schemas. This means that a single stored outline could be used for two tables with the same name but
located in a different schema! Once again, you should be very careful, especially if you have a database with multiple
copies of the same objects.
Whenever a SQL statement has a stored outline and, at the same time, a SQL profile and/or a SQL plan
baseline, the query optimizer uses only the stored outline. Of course, this is the case only when the usage of stored
outlines is active.
SQL Profiles
You can delegate SQL optimization to a component of the query optimizer called the
Automatic Tuning Optimizer
.
It might seem strange to delegate this task to the same component that isn't able to find an efficient execution plan in
the first place. In reality, the two situations are very different. In fact, in normal circumstances the query optimizer is
constrained to generate a sub-optimal execution plan because it must operate very quickly, typically in the subsecond
range. Instead, much more time can be given to the Automatic Tuning Optimizer to carry out an efficient execution
plan. Further, it can use time-consuming techniques such as what-if analyses and make strong utilization of dynamic
sampling techniques to verify its estimations.
The Automatic Tuning Optimizer is exposed through the
SQL Tuning Advisor
. Its aim is to analyze SQL
statements and to advise how to enhance their performance by either gathering missing or stale object statistics,
creating new indexes, altering the SQL statement, or accepting a SQL profile. The following sections are dedicated to
advice related to SQL profiles.
It's essential to understand that SQL profiles, officially, can be generated only through the SQL Tuning Advisor.
Nevertheless, as I describe later in this section, you can also create them manually.
How It Works
The following sections describe what SQL profiles are and how to work with them. It also provides information
about their internal workings. To manage them, you can use a graphical interface that is integrated into Enterprise
Manager. We won't spend time here looking at that because, in my opinion, if you understand what is going on in the
background, you'll have no problem using the graphical interface.
What Are SQL Profiles?
A SQL profile is an object containing information that helps the query optimizer find an efficient execution plan for
a specific SQL statement. It provides information about the execution environment, object statistics, and corrections
related to the estimations performed by the query optimizer. One of its main advantages is the ability to influence
the query optimizer without modifying the SQL statement or the execution environment of the session executing it.
In other words, it's transparent to the application connected to the database engine. To understand how SQL profiles
work, let's look at how they're generated and utilized.
Figure
11-4
illustrates the steps carried out during the generation of a SQL profile. Simply put, the user asks the
SQL Tuning Advisor to optimize a SQL statement, and when a SQL profile is proposed, he accepts it.
