 |
|
Initialization
Parameters that Affect CBO Index Behavior
Oracle Tips by
Burleson
|
Several initialization parameters control the
way that the cost-based optimizer treats indexes.
The fast_full_scan_enabled Parameter
The fast_full_scan_enabled parameter is
obsolete in Oracle8i, where it is the default behavior, but in
releases prior to Oracle8i, it controls whether the optimizer
will consider an index fast full scan when deriving the execution plan
for your query. It is important to note that the
fast_full_scan_enabled parameter does not disable the
index_ffs hint but instead controls whether CBO will consider
index fast full scans in the absence of any cost-based hints.
If you want to force a range scan rather than a
fast full scan at the SQL statement level, you can use the
index_asc and index_desc hints.
The optimizer_index_cost_adj Parameter
The optimizer_index_cost_adj parameter
is an initialization parameter that can be very useful for SQL tuning.
It is a numeric parameter with values from zero to 1,000 and a default
value of 1,000. It can also be enabled at the session level by using
the alter session set optimizer_index_caching = nn syntax. This
parameter lets you tune the optimizer behavior for access path
selection to be more or less index friendly, and it is very useful
when you feel that the default behavior for the CBO favors full-table
scans over index scans.
The default value is 1,000, and any value less
than 1,000 makes the CBO view indexes less expensive. If you do not
like the propensity of the CBO first_rows mode to favor full-table
scans, you can lower the value of optimizer_index_cost_adj to
10, thereby telling the CBO to always favor index scans over
full-table scans.
TIP: If you are having slow performance
because the CBO first_rows mode is favoring too many full-table scans,
you can reset the optimizer_index_cost_adj parameter to
immediately tune all of the SQL in your database to favor index scans
over full-table scans. This is a “silver bullet” that can improve the
performance of an entire database in cases where the database is OTLP
and you have verified that the full-table scan costing is too low.
Even in Oracle 8.1.7, the CBO sometimes falsely
determines that the cost of full-table scan is less than the cost of
an index access. The optimizer_index_cost_adj parameter is a
great approach to whole-system SQL tuning, but you will need to
evaluate the overall effect by slowly resetting the value down from
1,000 and observing the percentage of full-tale scans. You can also
slowly bump down the value of optimizer_index_cost_adj when you
bounce the database and then either use the access.sql scripts
(see Chapter 9) or reexamine SQL from the STATSPACK
stats$sql_summary table to see the net effect of index scans on
the whole database.
Conclusion
This chapter has been concerned with the
default behavior of the cost-based optimizer and has explored how the
CBO handles complex table joins, subqueries, and complex Boolean
expressions. We also investigated some important initialization
parameters and showed how they affect the costing estimates for
cost-based optimization.
The major points in this chapter include these:
-
When using the CBO, you must make a
philosophical choice between dynamic execution plans and static
execution plans. This decision affects your use of stored outlines
and how often you reanalyze CBO statistics.
-
Oracle provides several CBO hints that can
improve the choice of join methods.
-
For data warehouse queries that join more than
five tables, the optimizer_max_permutations parameter can be
used to ensure that a query evaluates all possible table join
combinations. After tuning, it is critical that these queries use
stored outlines to avoid SQL parse times that can often exceed 30
minutes.
-
You can change the default behavior of the CBO
by adjusting numerous initialization parameters. This approach often
allows you to establish a very fast baseline before starting the
individual tuning of SQL statements.
-
The CBO sometimes falsely determines that the
cost of a full-table scan is less than the cost of an index access.
The optimizer_index_cost_adj parameter will change the
costing for index scans, making them more attractive.
Next, let’s take a look at tuning with the
rule-based optimizer and see how it can be used to tune SQL queries.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
