Question: What is the Oracle 12c
adaptive optimization procedures that are being described on
the web?
Answer: In a
nutshell, Oracle 12c adaptive optimization is the ability of
Oracle to learn from its mistakes. Whenever Oracle notices
that he has miscalculated a cardinality estimate, he stores
the "real" cardinality such that subsequent SQL executions
till be faster then the initial execution. Also see
my detailed notes on
adaptive execution plans.
In a nutshell, the
Oracle optimizer postpones the decision of what types of
table join to use (e.g. hash join, nested loops join) until
run-time, after the Oracle execution has had a chance to
inspect the characteristics of the tables being joined.
This gets around the issue of staleness of CBO statistics
and is analogous to the Oracle dynamic damping utility,
first introduced in Oracle 10g.
NOTE:
Oracle dynamic sampling has been re-named to
dynamic (or adaptive)
statistics in 12c.
At run time, the Oracle
optimizer begins a nested loops join, and buffers
the data until it has a chance to see whether a hash join
might be a faster join technique. If so, Oracle will
abandon the bested loops join and change it into a hash
join, all during query execution.
This adaptive optimization approach acknowledges that it
can be impossible for any software program to always guess
cardinality correctly, and cardinality determines
unimportant SQL optimization steps, such as table join
order.
If the optimizer notices that he came up
with a sub-optimal SQL execution plan (i.e. wrong table join
type), the optimizer will take action to correct the error,
before fetching the data:
1 – Store the “real” stats in the
dictionary (SYSAUX tablespace) as “adaptive statistics” (a..k.a.
dynamic statistics)
2 - Take the change into
consideration for later queries, using the “real”
cardinality in lieu of the cardinality estimate.
3 –
Note a new step called a “STATISTICS COLLECTOR” in a tkprof
(SQL Trace).
4 - The resulting stats are cached as
shared dynamic stats specific for the statement, including
the bind values. This information can be used by any session
using the same SQL query.
Oracle notes that It is also possible to put adaptive
join methods into reporting mode by setting the init.ora
parameter optimizer_adaptive_reporting_only=true.
When
optimizer_adaptive_reporting_only=true,
the information needed to enable adaptive join methods is
gathered, but no action is taken to change the execution
plans. This effectively turn-off dynamic statistics
because the default value is
optimizer_adaptive_reporting_only=false.
