Tuning with the Cost-Based Optimizer
Oracle Tips by Burleson Consulting
This chapter is devoted to techniques for
getting the most out of the cost-based optimizer when tuning SQL.
The material in this chapter is intended to provide a general
framework for SQL tuning. The topics in this chapter include:
- Cost-based optimization and SQL tuning
- Cost-based initialization parameters
- Analyzing tables and indexes
- Using STATSPACK to monitor execution plans
Most Oracle databases have a hodgepodge of SQL
statements that use hints and have queries that execute in both
rule-based optimization and cost-based optimization. Hence, the
focus of this chapter will be tuning your database when you have a
cost-based default optimizer_mode, either all_rows or first_rows.
Statistics and Cost-Based Optimization
One of the requirements of cost-based
optimization is the presence of statistics on tables and indexes.
For tables, the statistics give a general idea of the number of rows
in the table, the average row length within the table, and other
statistics that are gathered by examining sample rows from the
table. As you know, table statistics are generated using the
analyze table command syntax. For indexes, the analyze index
command is used in order to gather information about each index that
participates in the cost-based query.
The statistics for indexes include
information about the number of distinct values within the index,
the number of entries in the index, and the physical storage
characteristics of the index within the tablespace. In the case
where an index has been analyzed for column histograms, we also see
individual buckets that are created with statistics about the
distribution of data within the index. As you know, column
histograms are only useful in cases where an index has a highly
skewed distribution of values, such that a query against one value
in the index is faster with a full-table scan, whereas another query
against the same index with a different column value would be better
served by using an index range scan.
statistics are used in conjunction with the cost-based optimizer to
formulate a decision tree of different execution plans, each with an
estimated cost for each plan (Figure 14-1).
Figure 1: Using
statistics with the cost-based optimizer
The optimizer then evaluates the plan tree and
chooses the execution plan with the lowest estimated cost.
Dynamic versus Static CBO Execution Plan Philosophy
While the basic concept of using the table
and index characteristics in the formulation of the execution plan
is quite good, there is a philosophical choice that needs to be made
regarding the dynamic nature of SQL execution plans. The whole idea
behind periodically reanalyzing tables and indexes is that the
execution plans for your SQL may change as the characteristics of
the data change. When adopting this dynamic approach, you must give
full faith to the cost-based optimizer to choose the appropriate
execution plan, and you cannot use optimizer plan stability (stored
outlines) to make the SQL changes permanent. If you adopt the
dynamic SQL execution philosophy, you may tune your SQL only by
physically changing your SQL by adding hints to the source code. You
must also ensure that any hints that you add are general enough
(e.g., the first_rows hint) to allow the optimizer to
evaluate several execution plans.
On the other hand, some SQL tuning
professionals subscribe to the notion that there exists only one
appropriate execution plan for any given SQL query, and they utilize
stored outlines to ensure that once tuned, the same execution plan
is always invoked. When adopting the static philosophy, the only
exception to this rule is the case of skewed indexes, where the SQL
tuning professional deliberately does not create stored outlines,
allowing the optimizer to detect the most appropriate access plan
given the skew of the index columns.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.