Database Reference
In-Depth Information
Chapter 11
SQL Optimization Techniques
Whenever the query optimizer is unable to automatically generate an efficient execution plan, some manual
optimization is required. For that purpose, Oracle Database provides several techniques, summarized in Table 11-1 .
The goal of this chapter isn't only to describe these techniques in detail but to explain what each technique can do for
you and in which situations you can take advantage of them. To choose one of them, it's essential to ask yourself three
basic questions:
Is the SQL statement known and static?
Should the measures to be taken have an impact on a single SQL statement or on
all SQL statements executed by a single session (or even on the whole system)?
Is it possible to change the SQL statement?
Table 11-1. SQL Optimization Techniques and Their Impacts
Technique
System
Session
SQL Statement
Availability
Altering the access structures
All versions
Altering the SQL statement
•*
All versions
Hints
•*
All versions
Altering the execution
environment
•*
All versions
Stored outlines
All versions
SQL profiles
All versions
SQL Plan Management
As of version 11.1
* You have to change the SQL statement to use this technique.
The Tuning Pack and, therefore, Enterprise Edition are required.
Enterprise Edition is required.
Let me explain why these three questions are so important. First, sometimes the SQL statements are simply
unknown because they're generated at runtime and change virtually for each execution. In other situations, the
query optimizer can't correctly deal with specific constructs (such as a restriction in the WHERE clause that can't be
applied through an index) that are used by lots of SQL statements. In both cases, you have to use techniques that solve
the problem at the session or system level, not at the SQL statement level. This fact leads to two main problems. On
one hand, as summarized in Table 11-1 , several techniques can be used only for specific SQL statements. They're
 
 
Search WWH ::




Custom Search