Database Reference
In-Depth Information
Part III
Query Optimizer
Make the best use of what is in your power, and take the rest as it happens.
—Epictetus
1
Every single SQL statement sent to the database before being processed by the SQL engine must be turned
into an execution plan. In fact, an application specifies only what data must be processed through SQL
statements, not how to process it. The aim of the query optimizer isn't only to produce execution plans
describing how to process data but also, and most important, to deliver efficient execution plans. Failing to do
so may lead to abysmal performance. Precisely for this reason, a book about database performance must deal
with the query optimizer.
The aim of this part, however, isn't to cover the internal workings of the query optimizer. Instead, a very
pragmatic approach is presented here, aimed at describing the essential features of the query optimizer you
have to know. Chapter 6 introduces basic concepts and the architecture of the query optimizer. Chapter 7
and 8 discuss the statistics used by the query optimizer. Chapter 9 describes the initialization parameters
influencing the behaviour of the query optimizer and how to set them. Finally, Chapter 10 outlines different
methods of obtaining execution plans, as well as how to read them and recognize inefficient ones.
Two main query optimizers are available in Oracle Database, the
rule-based optimizer
(RBO) and the
cost-based optimizer
(CBO). As of Oracle Database 10g, the use of the rule-based optimizer is no longer
supported and, therefore, won't be covered here. Throughout this topic, when you read the term
query
optimizer
, I always mean the cost-based optimizer.
