 |
|
Cost-Based Optimization
and SQL Tuning
Oracle Tips by
Burleson
|
Unless you have a brand-new system that was
developed on Oracle8i, chances are that you've already had some
experience with running both cost-based optimization and rule-based
optimization together inside the same database. As the cost-based
optimizer continues to improve, more and more SQL statements can be
moved from rule-based optimization into cost-based optimization, and
this is especially true in Oracle8i. However, it is not
immediately obvious which of your SQL statements will benefit from a
cost-based optimization approach, and there is no substitute for
manually tuning and evaluating the elapsed speed of each query to find
the fastest execution plan.
Prior to Oracle8i, most Oracle systems
used the default optimizer_mode=rule and tuned individual SQL
statements with cost-based hints to take advantage of the new Oracle8
features such as function-based and bitmap indexes. However, now that
Oracle8i’s cost-based optimizer has a reach the point where it
is compatible with rule-based optimization, many SQL tuning
professionals are experimenting with using a cost-based default
optimizer_mode and then individually tuning queries to achieve the
fastest execution plans.
As I noted in our review of Oracle hints in
Chapter 12, you have a huge number of options available to use to tune
when using the cost-based optimizer. Some of the new features that we
can take advantage of with cost-based optimization include bitmap
indexes, function-based indexes, star query joins, materialized views,
and a host of other new hints that are all designed to improve the
response time of our Oracle SQL statements.
As I noted in Chapter 7, the first thing most
SQL tuning professionals do when they encounter a suspect SQL program
is to add a rule hint and see if the execution plan becomes
more efficient. Even under cost-based optimization, this is still a
very good approach to take because the rule-based optimizer in general
will provide very good execution plans except when cost-based indexes
are present. The RBO does not understand function-based or bitmap
indexes and ignores them when developing an execution plan.
When running your database with a cost-based
default, there are particular subsets of SQL queries that are the most
important for your SQL tuning effort. Simple queries that select from
a single table, or DML statements such as inserts and updates, are not
as important as complex SQL queries. By complex, I mean those kinds of
queries that join multiple tables together, including subqueries and
queries that have very complicated Boolean predicates in the SQL
where clause. These are the types of SQL statements that will
benefit most from tuning.
While we have already covered the basic methods
for tuning SQL statements in earlier chapters, it is helpful to
categorize the cost-based SQL tuning options according to the type of
access that is being performed. We generally see three classes of
queries:
- Cost-based multiple table join techniques (see
also Chapter 16)
- Cost-based subquery optimization techniques
(see also Chapter 19)
- Queries with complex Boolean predicates in the
where clause
Within each of these areas, there are sets of
rules that direct us to possible hints that can be applied to these
classes of queries in order to improve the overall execution speed. As
I noted over and over again in earlier chapters, the only way to tell
for sure if a hint is going to improve the execution speed of the
query to actually execute the query. Even with Oracle8i,
costing estimates from the cost-based optimizer do not provide as
accurate a measure of the real execution speed of the query as does
actually running the query and timing the query elapsed time with the
SQL*Plus set timing on command.
The cost-based optimizer uses “statistics” that
are collected from the table using the analyze table and
analyze index commands. Oracle uses these metrics about the tables
in order to intelligently determine the most efficient way of
servicing the SQL query. It is important to recognize that in many
cases the cost-based optimizer may not always make the proper decision
in terms of the speed of the query, and Oracle has provided numerous
hints to allow the DBA and developer to tune the execution plan. The
cost-based optimizer is constantly being improved, but there are still
many cases where the rule-based optimizer will result in faster Oracle
queries. One of the first things a seasoned Oracle DBA does when
tuning a SQL statement is to add a rule hint, or use the
alter session set optimizer_goal = rule statement in order
to change the default optimizer mode from cost-based to rule-based
optimization.
Before retrieving any rows, the cost-based
optimizer must create an execution plan that tells Oracle the
order in which to access the desired tables and indexes. The
cost-based optimizer works by weighing the relative “costs” for the
different execution paths to the data, and it chooses the path with
the smallest relative cost. Once the statistics have been collected,
there are three ways to invoke the cost-based optimizer:
-
Setting the init.ora parameter
optimizer_mode = all_rows, first_rows, or choose
-
ALTER SESSION SET optimizer_goal=all_rows
or first_rows
-
Cost-based hints /*+ all_rows */ or
––+ all_rows
These “costs” for a query are determined with the aid of
table and index statistics that are computed with the analyze table
and analyze index commands in Oracle.
When we begin to tune a SQL statement with a
cost-based optimizer default, the first step is to take a look at the
suspect query and categorize the query according to the type of table
access method. Once we've developed a pattern for each SQL statement,
it becomes very easy to go to the select set of hints that may be able
to improve the execution time for the query. To illustrate, the
following sections will examine each one of the basic SQL models and
explore the types of hints that can be used under cost-based
optimization to improve the execution plan for the query.
Let’s take a look at each of these three
categories of queries: table joins, subqueries, and complex Boolean
expressions.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
