Question: How do I
use the optimizer_adaptive_features parameter?
Answer: Oracle notes that
optimizer_adaptive_features enables or disables all of the
adaptive optimizer features, including adaptive plan
(adaptive join methods and bitmap plans), automatic
re-optimization, SQL plan directives, and adaptive
distribution methods.
The optimizer_adaptive_features
parameter is part of the Oracle12c adaptive execution
plans are a add-on to Oracle dynamic sampling, a new feature
of dynamic statistics.
Also see
Oracle
12c adaptive optimization of SQL.
Oracle is striving to get over an
ongoing issue with always having enough detail in the
metadata to always choose the "best" execution plan, and
Oracle is now working towards an adaptive, "self-learning"
optimizer.
In this new approach, "real-time"
statistics collection is done at query execution time, and
the optimizer has the option of changing a tables join
methods between a hash join to a nested loops join, well
after the original plan was generated!
Join statistics are now monitored for
executing SQL statements and a new v$sql column
is_reoptimizable has been added to indicate when a
query will benefit from dynamic statistics.
This funky new functionality is
controlled by the parameters
optimizer_adaptive_reporting_only and
optimizer_adaptive_features and
optimizer_adaptive_ plans. This can also be used
a hint in a query: with the opt_param directive
select /*+
OPT_PARAM('_optimizer_adaptive_plans','true') */
customer_name
from
customer;
See my
notes on
adaptive execution plans and
12c adaptive SQL optimization.
Also see my
bind_aware hint tips.
