Database Reference
In-Depth Information
Set the Right Parameter!
It's obvious that Oracle doesn't just randomly provide new initialization parameters. Instead, each initialization
parameter is introduced to control a very specific feature or behavior of the query optimizer. At the risk of repeating
myself, I must remind you that Oracle's introduction of a parameter implies that no single value can be applied to all
situations. Thus, for each initialization parameter, you must infer a sensible value from both the application workload
profile and the system where the database engine runs.
To perform a successful configuration of the query optimizer, it's essential to understand how it works and the
impact each initialization parameter has on it. With this knowledge, instead of tweaking the configuration randomly
or copying the “good values” from an article you found recently on the Internet, you should do the following:
•
Understand the current situation. For example, why has the query optimizer chosen an
execution plan that is suboptimal?
•
Determine the goal to be achieved. In other words, what execution plan do you want to achieve?
•
Find out which initialization parameters, or possibly which statistics, should be rectified
to achieve the goal you set. Of course, in some situations it isn't enough to set initialization
parameters only. It may be necessary to modify the SQL statement and/or the database design
as well.
The following sections describe how some of the initialization parameters referenced by the configuration road
map of Figure
9-1
work and also give advice on how to find good values for your system. The parameters that aren't
described in this chapter are covered elsewhere in the topic while covering the feature they control. Parameters
are divided into two groups: one where only the operation of the query optimizer is affected, and the other where
parameters have to do with the
program global area
(PGA).
Query Optimizer Parameters
The following sections describe a number of parameters related to the operation of the query optimizer.
optimizer_mode
The
optimizer_mode
initialization parameter is essential because with it you specify what the word
efficient
means
to the query optimizer. Generally speaking, it may mean “faster,” “using fewer resources,” or perhaps something else.
Because with a database you're processing data, you'll usually want to process it as fast as possible. Therefore, the
meaning of
efficient
should be “the fastest way to execute a SQL statement without wasting unnecessary resources.”
What that means for a SQL statement that's always completely executed (for example, an
INSERT
statement) is clear.
For a query, on the other hand, there are subtle differences. An application, for example, isn't obliged to fetch all rows
returned by a query. In other words, queries might be only partially executed.
Let me give you an example unrelated to Oracle Database. When I Google the term
query optimizer
, I get the
matching pages in best-ranking order, beginning with a page listing the first ten results (the ten best ones). On the
same page, I'm informed that there are about 986,000 pages in the result set and that the search took 0.26 seconds.
This is a good example of processing that is optimized to deliver the initial data as fast as possible, because the first
few pages are almost always the only ones that will be actually accessed by the users. To access one of the pages, I then
click the corresponding link. At this point I'm usually not interested in getting only the first few lines. I want the whole
page to be available and correctly formatted, at which point I'll start reading. In this case, the processing should be
optimized to provide all data as fast as possible and not only pieces. Every application (or part of it) falls into one of
these two categories. Either fast delivery of the first part of the result set is important or the fast delivery of the whole
result set (that is equivalent to the fast delivery of the last row) is important.
