 |
|
|
|
|
|
If you have been reading this book in the order the chapters have been presented, you have covered
a lot of ground, from the basics of the Oracle database to more advanced development topics. But
this book is not completely finished, just as your job is not finished once you successfully finish the
creation of an application. After development comes deployment, and one of the key indicators of a
successful deployment is an application that performs well.
Performance, of course, means different things to different interest groups. Your performance may
be judged by how well you meet deadlines in the creation of your application or module. Adatabase
administrator may judge performance by how much CPU or memory is used by an Oracle instance.
But to your constituency of users, performance is primarily determined by a judgment of response
time, or how quickly data is returned from the Oracle database, compared to their expectation of
that response time.
Of course, response time is based on many factors, such as the amount of available resources on
the Oracle server and the network and the design of the database and the application. But you
should still strive for the best performance available in your environment, performance that is
based on the properly optimized retrieval of requested information.
What Is Optimization?
The outcome just described comes as the result of the work of the Oracle optimizer. This part of
the Oracle database is responsible for discovering the best way to retrieve information requested
by a user. To understand the need for an optimizer, you need to step back and remind yourself of
one of the key premises of relational databases.
One of the primary benefits seen for relational technology was the ability to separate the physical
access of data, which is done by the database, with the logical request for data, specified through
an SQL statement. An SQL statement does not have to specify how to retrieve data—just what
data was desired.
We have already discussed how the Oracle database can make a choice between retrieving data directly
from the data blocks of the table, or use an index to direct the query to the relevant data. Take this simple
situation and compound it with a table with many indexes, or a query that requests data from multiple
tables, and add in additional requirements, such as the need to select data, sort data or group data, and
calculate aggregate values, and you can see that an Oracle database may face a wide number of access
choices.
The purpose of the optimizer is to sort through these choices and come up with the best plan of attack
for data retrieval, which is known as the execution plan. A clever optimizer takes into account the presence
of indexes, the relative speed of various access methods, and the resources available to the database
server, as well as the specific access directives in the query. Oracle 10g Database has a clever optimizer
that is based on the cost of these various access choices. Prior to Oracle 10g, the database allowed you to
choose another type of optimizer, a rule-based optimizer, which predated the cost-based optimizer. The
following section explains the different types of optimizers, since many of you may be using earlier versions
of the Oracle database, and so may still be able to use the rule-based optimizer.The Oracle Optimizer
The Oracle database has had an optimizer for a very long time. Initially, the Oracle database used a
rule-based optimizer. This optimizer made its decisions about execution plans based on a series of rules,
which were evaluated in a fixed order. The rules made perfect sense and were the result of many years
of working with the Oracle database and its users.
However, the rule-based optimizer suffered from some faults, too. First, the set of rules used were finite,
and in that sense, somewhat limited in the complexity they could encompass. As the complexity of the
Oracle database grew with the introduction of new features, such as materialized views and function-based
indexes, the rule-based optimizer was not enhanced to take advantage of them.
The rule-based optimizer had another basic flaw. Sometimes a choice between two different steps in an
execution plan would end up in a tie—the rule-based optimizer could not determine which choice was
more efficient. In these situations, the tie was resolved by looking at the order of the tables in the SQL
query. The result of this form of tie-breaking meant that the same logical query could generate two
different execution plans, based on the order of tables in the statement. This outcome flies in the face of
relational theory, which requires that an SQL statement should be independent of the order of entities,
when a simple re-ordering of the table could result in different execution plans and different performance.
Besides, this anomaly meant that you, the developer, would have to be aware of things like the composition
of the database in order to properly write your SQL. You certainly don’t need that!
In version 7, Oracle introduced the cost-based optimizer. The cost-based optimizer can be considered to
consist of shades of gray in contrast to the black-and-white decision-making process of the rule-based
optimizer. With the cost-based optimizer, the Oracle database is able to consider resource usage, or cost,
when it made decisions about which steps to use in an execution plan.
You saw the cost-based optimizer at work in one of the examples in Chapter 10 on indexes. The Oracle
database determined that retrieving a small number of rows would be optimally executed by using an
index, while retrieving more rows would be faster by directly accessing the data blocks of the table.
690
Chapter 28
Initially, some Oracle users did not use the cost-based optimizers, based on the suspicion that it would
not work as well as the rule-based optimizer. In the early versions of the cost-based optimizer, this
suspicion was sometimes based on evidence. In the years since its introduction, the quality of the decisions
made by the cost-based optimizer has improved, and new features of the Oracle database, such as bitmap
and function-based indexes, have been taken into account by the cost-based optimizer, but not the
rule-based optimizer.
In earlier books, we would have advised you to embrace the cost-based optimizer because of its advanced
sophistication. With the release of Oracle 10g, we can merely state that the rule-based optimizer is no
longer officially supported. The remainder of this chapter will exclusively concentrate on the cost-based
optimizer, referred to simply as the optimizer from here on.SapereOnLine |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
