Database Reference
In-Depth Information
That said the query optimizer works well, meaning it generates good execution plans for most
2
SQL statements.
Be careful, though, because this is true only on the condition that the query optimizer is correctly configured and
the database has been designed to take advantage of all its features. I can't stress this enough. Also note that the
configuration of the query optimizer includes not only the initialization parameters but the system statistics and
object statistics as well.
Configuration Road Map
Because there's no such thing as a magic configuration, we need a solid and reliable procedure to help us. Figure
9-1
sums up the main steps I go through. Their description is the following:
1.
Two initialization parameters should always be adjusted:
optimizer_mode
and
db_file_multiblock_read_count
. As you'll see later in this chapter, the latter isn't always
relevant for the query optimizer itself. Nevertheless, the performance of some operations
may strongly depend on it.
2.
Because the default values of the initialization parameters adjusted in this step are
generally good, this step is optional. In any case, the aim of this step is to enable or disable
specific features of the query optimizer.
3.
Because system statistics and object statistics provide vital information to the query
optimizer, they must be gathered.
By setting the
workarea_size_policy
initialization parameter, the choice is made
between manual and automatic sizing of work areas provided to operations storing data in
memory. Depending on the method chosen, other initialization parameters are set either
in step 5 or in step 6.
4.
If the sizing of work areas is automatic, the
pga_aggregate_target
initialization parameter
is set. Optionally, as of version 12.1, the
pga_aggregate_limit
initialization parameter can
be changed as well.
5.
6.
If the sizing of work areas is manual, the actual size depends on the type of operation using
the memory. Basically, a specific initialization parameter is set for each type of operation.
7.
When the first part of the configuration is in place, it's time to test the application. During
the test, the execution plans are collected for the components that don't provide the
required performance. By analyzing these execution plans, you should be able to infer
what the problem is. Note that at this stage, it's important to recognize general, not
individual, behavior. For example, you may notice that the query optimizer uses too many
or too few indexes or doesn't recognize restrictions correctly.
8.
If the query optimizer generates efficient execution plans for most SQL statements, the
configuration is good. If not, you proceed to step 9.
9.
If the query optimizer tends to use too many or too few indexes or nested loops, it's
usually possible to adjust the
optimizer_index_caching
and
optimizer_index_cost_adj
initialization parameters to fix the problem. If the query optimizer makes big mistakes in
the estimation of cardinalities, it's possible that some histograms are missing or need to
be adjusted. Also adjusting dynamic sampling might help. As of version 11.1, extended
statistics might also help.
2
Perfection is unrealizable in software development as in almost any other activity you can imagine. This rule, even if neither you
nor Oracle likes it, applies to the query optimizer as well. You should therefore expect that a small percentage of the SQL
