Enabling Oracle 12.2 ADAPTIVE Features in Oracle

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS. And the key trigger to cure potential and known performance issues in Oracle Database is enabling Oracle 12.2 ADAPTIVE Features in Oracle

For more information please see:

Enabling Oracle 12.2 ADAPTIVE Features in Oracle

But Oracle Database 12.2 on-premises is not out yet – so what should you do when upgrading to Oracle Database 12.1 – or struggling with some of the “adaptive” features in Oracle 12.1?

It recommends to adopt the Oracle Database 12.2 defaults when upgrading to Oracle Database 12.1. This can be achieved by installing two patches – we call it the recommended approach.

    • The patch for bug# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
    • The patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.

Please make sure you’ll remove OPTIMIZER_ADAPTIVE_FEATURES from your spfile:

alter system reset optimizer_adaptive_features;

when applying the patches.

Enabling Oracle 12.2 ADAPTIVE Features in Oracle

Adaptive Features – backporting Oracle 12.2 parameters into Oracle 12.1

Both patches should help as well once you have upgraded already to Oracle Database 12.1 but encountering performance issues.

Please note that it’s not necessary to set OPTIMIZER_DYNAMIC_SAMPLING to a
non-default value because the patches will disable the use of adaptive dynamic sampling
to match the default behavior in Oracle Database 12.2 when both new parameters are used in their default settings.

Further Information:


Share this: