Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS.

For more information please see:

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.

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:

–Mike

9 thoughts on “Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

  1. Hello,

    patch 22652097 is currently available only as overlay on top of PSU July 2016 and only for Linux x64 and AIX. I have requested an overlay on top of DBBP Proactive October 2016.

    Regards,
    Martin

  2. Nice Post, thanks. But why is the patch for bug# 22652097 only available for the PSU branch and not for BP, which is said to be the future for 12c?

    BTW: Enjoyed your Presentations at DOAG 🙂

  3. Same problem! Just checked: patch 22652097 on top of DBBP-Oct is still with development. Should we request an overlay every time the new PSU and DBBP released?

    — Kirill Loifman

  4. Hi Kiril,

    I see your point, I fully understand it, I agree with you – and still I can’t change it. I checked 226… for another customer last Friday and it was escalated already by then. I can only ask you to open an SR and request it as well. The internal automatism will increase the SR COUNTER in the bug and thus show how many people are affected.

    Patching is not in our hands unfortunately.

    Sorry for the inconvenience and kind regards
    Mike

  5. This is the list of fixes for the Proactive Bundle Patch Jan17:
    https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=363486125922311&id=1937782.1&_adf.ctrl-state=op9njo7xy_119

    And this is the one for the PSU Jan17 for 12c:
    https://support.oracle.com/epmos/faces/DocumentDisplay?id=1924126.1

    And none of them is included. The first one is obvious as it would massively change the setup by introducing two new parameters making one other obsolete. So this one will have to be applied always on top (unfortunately over and over again with every PSU or BP).

    The other one is not included either – and I don’t know if it will be included in a future Proactive BP. I would hope so but you’ll have to double check with support and ask for an inclusion.

    Cheers
    Mike

  6. Pingback: OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2 | Upgrade your Database - NOW!

  7. Pingback: On 12.1 + facing performance problems ? | Rogerio Eguchi Blog

  8. Pingback: Adaptive mayhem | Oracle Scratchpad

Leave a Reply

Your email address will not be published. Required fields are marked *