optimizer_adaptive_features

optimizer_adaptive_features

During the compilation of a SQL statement, the optimizer decides whether to use dynamic statistics by considering whether the available statistics are sufficient to generate an optimal execution plan. If the available statistics are insufficient, then the optimizer uses dynamic statistics to augment the statistics. With adaptive dynamic statistics (ADS), Oracle uses dynamic statistics automatically when the optimizer deems it necessary. The resulting statistics are persistent in the statistics repository, making them available to other queries.

Adaptive query optimization controls only adaptive dynamic statistics and not the default dynamic statistics.

When optimizer_adaptive_features (or optimizer_adaptive_statistics) = TRUE and optimizer_dynamic_sampling = 2 (default), then ADS can happen for parallel queries.

When optimizer_adaptive_features (or optimizer_adaptive_statistics) = FALSE and optimizer_dynamic_sampling is not 11, then ADS will not happen. However, the default dynamic sampling level is still honored.

If optimizer_dynamic_sampling is set to 11, then ADS is enabled for all queries irrespective of setting of the parameter optimizer_adaptive_features or optimizer_adaptive_statistics.

ADS is always used when SQL Plan Directives request dynamic sampling.