Database Reference
In-Depth Information
Chapter
14
Optimizers Are Not Perfect
ž
Optimizers do not always see the best alternative
ž
Matching and screening problems
ž
Non-BT predicates
ž
Unnecessary sorts
ž
Unnecessary table touches
ž
Optimizers' cost estimates are sometimes wrong, perhaps disastrously so
(and how we can help)
ž
Range predicates
ž
Skewed distribution
ž
Correlated columns
ž
Properties of partial index keys
ž
Optimizers' cost estimation formulas considerations
ž
I/O estimation
ž
CPU estimation
ž
Impact of pools and cache
ž
Helping the optimizer with estimation-related problems
ž
Optimize every time with actual values
ž
Access path hints
ž
Redundant predicates
ž
Falsifying statistics
ž
Modifying indexes
ž
Effect of optimizer problems on index design
INTRODUCTION
Sometimes a SELECT call can be unbelievably slow even though suitable indexes
have been created for it. If the dominant component is service time and not queu-
ing time, the optimizer has probably chosen a poor access path. The optimizer
of a relational DBMS has a difficult task; it must choose the right access path
based solely on the statistics collected for the characteristics of the tables and
Search WWH ::
Custom Search