Oracle Database 12c | Wednesday, November 23, 2016

Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

Browser Redirect to the new Oracle Database Upgrade Blog in 3 seconds
This blog post has been moved to the new Oracle Database Upgrade Blog:


https://mikedietrichde.com/2016/11/23/enabling-adaptive-features-of-oracle-12-2-in-12-1/


Please update your bookmarks as well - THANKS!!





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
.

Join the discussion

Comments ( 6 )
  • Martin Decker Wednesday, November 23, 2016

    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


  • Björn B. Tuesday, November 29, 2016

    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 :)


  • Kirill Loifman Thursday, December 8, 2016

    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


  • Mike Thursday, December 8, 2016

    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


  • Mark Wednesday, February 1, 2017

    Hello,

    Do you know if the two patches have been included in any 12.1.0.2 database PSU yet?

    Thanks

    Mark


  • Mike Wednesday, February 8, 2017

    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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services