 |
|
Generating the SQL Execution Plan
Oracle Tips by Burleson Consulting |
Because Oracle SQL is a declarative data
access language, the SQL statement does not specify the navigation
path to service the request. While using SQL as a purely declarative
language is a great goal, in practice Oracle has found that hints
are sometimes required to ensure that the optimizer generates an
optimal execution plan. The job of the Oracle optimizer is to
determine the fastest and most efficient way to service the query.
When we talk about optimizing a query, you need to remember that
Oracle makes a distinction between the speed of a query and the
efficiency of a query. Oracle has two optimizer goals. Each of these
goals reflects a general approach to satisfying SQL requests.
-
Maximize speed This
goal focuses on returning the result set in the minimal amount of
elapsed time. This goal is common for online transaction
processing systems (OLTP), where fast response time is the primary
goal. This goal is implemented with Oracle’s first_rows
optimizer mode.
-
Minimize resources This
goal concentrates of servicing the query while using the minimum
amount of machine and disk resources. This goal is most
appropriate for batch-oriented Oracle databases where the speed of
query completion is not a major concern. This goal is implemented
with Oracle’s all_rows optimizer mode.
While I will be going into great detail about the
optimizer mode in Chapter 4, suffice it to say that each Oracle
optimizer mode uses a different set of goals for determining the
optimal execution plan. The default optimizer mode for Oracle SQL is
set by the initialization parameter called optimizer_mode.
When Oracle accepts the parsed SQL to
generate the execution plan, it looks to the Oracle data dictionary
for guidance. The setting of the Oracle initialization parameters
and the presence of index and table statistics influence the
decision of the SQL optimizers.
Oracle uses two types of SQL optimizers, the
rule-based optimizer (RBO) and the cost-based optimizer (CBO).
Although I will be discussing each of these modes in Chapters 14 and
15, you need to understand that Oracle relies on data dictionary
information to determine the best method to retrieve the data. With
the CBO, Oracle uses statistics that are created from running the
analyze command, and with the RBO Oracle uses information about
indexes in the data dictionary.
Oracle's default optimizer mode is called
“choose.” In the “choose” optimizer mode, Oracle will execute the
rule-based optimizer if there are no statistics present for the
table, or it will execute the cost-based optimizer if statistics are
present. The danger with using the choose optimizer mode arises in
cases where one Oracle table in a complex query has statistics and
the other tables do not have statistics. When only some tables
contain statistics, Oracle will use the cost-based optimization and
estimate statistics (by sampling 5000 rows) for the other tables in
the query at run time. This can cause significant slowdown in the
performance of the individual query. Be careful when using the
choose option.
When you give Oracle the ability to choose
the optimizer mode, Oracle will favor the cost-based approach if
any of the tables in the query have statistics. (Statistics are
created with the analyze table command.) For example, if a
three-table join is specified in choose mode and statistics
exist for one of the three tables, Oracle will decide to use the
cost-based optimizer. When this happens, the Oracle CBO will inspect
the num_rows column of the Remote DBA_tables view. If
num_rows is null, Oracle still estimate statistics based on a
5000-row sample. If the num_rows column is zero, Oracle will
not perform a run-time table analysis. In short, if Oracle analyzes
the table at run time, this action will dramatically slow down the
query. Next, let’s take a look at the different types of table
access methods.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
