Database Reference
In-Depth Information
Chapter 9
Configuring the Query Optimizer
The query optimizer is directly responsible for the performance of SQL statements. For this reason, it makes sense to
take some time to configure it correctly. In fact, without an optimal configuration, the query optimizer may generate
inefficient execution plans that lead to poor performance.
The configuration of the query optimizer consists not only of several initialization parameters but also of system
statistics and object statistics (system and object statistics are described in Chapter 7 and Chapter 8, respectively).
This chapter describes how these initialization parameters and statistics influence the query optimizer and presents
a straightforward and pragmatic road map that will help you achieve a successful configuration.
■
The formulas provided in this chapter, with a single exception, aren't published by Oracle. Several tests
show that they're able to describe how the query optimizer estimates the cost of a given operation. In any case, they
neither claim to be precise nor claim to be correct in all situations. They're provided here to give you an idea of how an
initialization parameter or a statistic influences query optimizer estimations.
Caution
To Configure or Not to Configure...
Adapting a Kenyan proverb
1
to our situation here, I'd say, “Configuring the query optimizer is costly, but it's worth the
expense.” In practice, I have seen too many sites that underestimate the importance of a good configuration. From
time to time I even have heated discussions with people who say to me, “We don't need to spend time on individually
configuring the query optimizer for each database. We already have a set of initialization parameters that we use over
and over again on all our databases.” My first reply is, frequently, something like this: “Why would Oracle introduce
almost two dozen initialization parameters that are specific to the query optimizer if a single set works well on all
databases? They know what they're doing. If such a magic configuration existed, they would provide it by default
and make the initialization parameters undocumented.” I then continue by carefully explaining that such a magic
configuration doesn't exist because of these two reasons:
•
Each application has its own requirements and workload profile.
•
Each system, which is composed of different hardware and software components, has its own
characteristics.