Database Reference
In-Depth Information
Cardinality feedback
(also called statistics feedback):
Either because of complex
predicates or missing input information, the query optimizer can't always compute
accurate estimations. When the query optimizer recognizes that it's computing
low-quality estimates for a SQL statement, the generated execution plan is annotated.
The accuracy of the estimations is then checked after the SQL statement's execution.
If the actual and estimated values differ significantly, information about the correct values
is stored, and a re-optimization is forced at the next SQL statement execution. Note that
the re-optimization is forced to let the query optimizer take advantage of the information
gained during the first execution, thereby increasing the chances of producing an optimal
execution plan. This feature is available as of version 11.2 only.
The version of the Oracle Database software in use also determines which query optimizer features are available.
It's essential to recognize that even knowing the five numbers of the Oracle Database version is insufficient to exactly
know which features are available. You also need to know whether the release in use is Enterprise Edition or Standard
Edition. In addition, note that some patches might also control the availability of specific query optimizer features and
their behavior.
Architecture
The query optimizer, which can be decomposed in the
logical optimizer
and the
physical optimizer
, is just one of the
building blocks of the SQL engine.
