Fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP

Posted on October 19, 2017 by Optimizer Patch Recommendation 26

Finally … the two most important fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP (Bundle Patch). I’m absolutely happy about this. Almost all of my customer and many folks outside I’m dealing with on a regular basis had to request and wait for merge patches over and over again. And we all knew – also based on the experience of the Real World Performance Team – that these two patches are so important to stabilize Oracle 12.1.0.2 production systems.

You may find this ALERT on MyOracle Support:

MOS Note 2289719.1
ALERT: Oracle Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0

Which fixes are included?

Besides some other fixes these important fixes are included:

Please see this blog post for details and further links about the patches, and why you need to apply them – or better use the October 2017 BP: Enabling Oracle 12.2 Adaptive Features in Oracle 12.1.0.2.

Where are these fixes included?

The fixes are included in the Oracle Database 12.1.0.2 Proactive Bundle Patch (BP). They are NOT included in the PSUs. And they are not included in Oracle 12.2 RUs as there’s no need to include them. This is another reason why you should switch from PSUs to BPs whenever possible.

Interestingly enough the fixes were included already in the Windows Bundle Patches since the August 2017 BP.

Please note that these fixes are disabled by default. But with the new Automatic Fix Control Persistence Framework once you enable them they’ll stay enabled when you apply subsequent Bundle Patches.

More information?

Of course you will find more information in the README for Database Proactive Bundle Patch 12.1.0.2.171017 Patch 26635880. I quote it here:

This patch is supported by 12.2 adaptive features behavior in 12.1.0.2, but these changes are kept by default off. See My Oracle Support Document 2312911.1 Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics & 12c SQL Performance) (Doc ID 2312911.1) for more information about how to use 12.2 adaptive features behavior in 12.1.0.2 provided by the Database Proactive Bundle Patch.

Further information is provided in MOS Note: 2312911.1 – Recommendations for Adaptive Features in Oracle Database 12.1.

See also David Kurtz‘ (Accenture – Enkitec) experiences with Oracle PeopleSoft installations using the Adaptive Features fixes: https://mikedietrichde.com/2017/07/06/adpative-features-patches-oracle-peoplesoft/

What is your task now?

It’s very “simple”:

  1. Apply Database Proactive Bundle Patch 12.1.0.2.171017 Patch 26635880.
  2. Remove a potentially existing parameter optimizer_adaptive_features from your SPFILE.
  3. Switch on the patches. Please see MOS Note: 2312911.1 for detailed options.
    Either add to your init.ora/spfile: *._fix_control='26664361:7','16732417:1','20243268:1'
    Or use: alter system set "_fix_control"='26664361:7','16732417:1','20243268:1' scope=both;
    Be VERY careful not delete other fix control entries if any exist. This command will overwrite existing _fix_control settings.
  4. In addition OPTIONALLY you may enable also the other optimizer fixes included.
    Please see DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2 on how to display and enable the other fixes in addition.
  5. Restart the database. This is required in order to enable the fixes correctly.
  6. Finally run the script execstat.sql:
    connect / as sysdba
    @?/rdbms/admin/execstat.sql

You should be good by using the defaults for optimizer_adaptive_statistics=FALSE and optimizer_adaptive_plans=TRUE.

Be aware if you had optimizer_adaptive_features=FALSE before applying this BP you may encounter now plan changes due to optimizer_adaptive_plans=TRUE by default. I’d recommend to test and keep an eye on it but giving it a try. Actually two of my Oracle 12c reference customers have exceptionally good experiences with the “plan” part of the adaptive features.

And one last thing:
If you have an Oracle Multitenant deployment with Oracle 12.1.0.2 and plan to enable the highly recommended “adaptive” fixes AND the fixes under control of DBMS_OPTIM_BUNDLE you must edit your init.ora/SPFILE as otherwise the two calls overwrite each other vice versa. I’ll file a bug these days.

–Mike

Share this:

Tags:

You may also like...

26 Responses

  1. Chris says:
    December 25, 2017 at 17:38

    Hello Mike,
    I agree in principle – very useful, but unfortunately quite buggy. Gave us lots of headaches – see here https://wordpress.com/post/s234blog.wordpress.com/1006

    Reply
  2. Chris says:
    December 26, 2017 at 09:48

    Sorry here is the correct link :
    https://wordpress.com/read/feeds/59808770/posts/1702279861

    Reply
    • Mike.Dietrich says:
      January 8, 2018 at 13:34

      Thanks Chris.

      Do I understand your blog post correctly:
      You had to disable everything (optimizer_adaptive_plans, optimizer_adaptive_statistics (default: false) and make sure the _fix_control is off)?

      Thanks,
      Mike

      Reply
      • chris says:
        January 9, 2018 at 10:25

        No, we had only to set optimizer_adaptive_statistics = false. It was true for us after applying the BP – for whatever reason – an SR is open for that. _reporting we left true. Fix_control we did not touch – it stayed off .
        Best,
        Chris

        Reply
  3. Olivier says:
    January 16, 2018 at 09:25

    Hi there,

    I do not understand completely _fix_control=’26664361:7′,’16732417:1′,’20243268:1′

    if 26664361:7 means 22652097+21171382+20243268, why do we need to add 20243268:1 again ?

    Olivier

    As written in “Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance) (Doc ID 2312911.1)”
    To manually enable or disable the fixes, use fix control 26664361 to override the automatic detection. (NOTE: Fix control 26664361 is available only in the Proactive BP.)
    The fix control has three flags:
    1 = act like Patch 22652097 is installed
    2 = act like Patch 21171382 is installed
    4 = act like Patch 20243268 is installed

    Reply
    • Mike.Dietrich says:
      January 16, 2018 at 13:26

      Olivier,

      I can only reproduce what the support note says based on the information given by the developer:

      “The October 2017 Database Proactive Bundle Patch (BP), , has the fixes for both Bug 22652097 and Bug 21171382 included. It also includes the fix for Bug 20243268, which interacts with the fix for Bug 22652097. These fixes are disabled by default unless the installation detects the patches were already installed but replaced by the BP installation, or if they are enabled manually.”

      And further down below in the same Note:

      “The DBA has installed all three patches; use 7 (1+2+4):

      (spfile) alter system set “_fix_control”=’26664361:7′,’16732417:1′,’20243268:1′ scope=spfile;
      (pfile) Add to pfile:

      _fix_control=’26664361:7′,’16732417:1′,’20243268:1′
      ”

      But I see your point. 26664361 is now being used to capture status of 3 related inactive patches: 22652097, 21171382 and 20243268.
      And therefore adding 20243268 to the string looks like a duplication. It should be on already by the bit vector “7” for 26664361.

      Let me investigate this – I’ll get back to you.

      Cheers, Mike

      Reply
  4. Rudy says:
    January 18, 2018 at 11:18

    Hi Mike,

    We run 12.1.0.2 SE2.
    We didn’t install 22652097 because we were already running in prod and it would radically change things.
    We installed 21171382 because MOS doc “Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2” told us so.
    So we ended up with the adaptive statistics part of adaptive features enabled (the plans part is not available in SE2), and stats extensions off.
    We didn’t switch to BPs because only PSUs are recommended with SE2 and because we would have to rollback all the patches and reapply them to too many databases.
    Adaptive statistics runs pretty good but has a drawback: all the queries want dynamic sampling when you first run them, so when you start up the instance ALL the queries want dynamic sampling, resulting in a surge of I/O until all plans are computed – and a slow database as well.

    What would you suggest for us to do?
    Re-enable stats extensions?

    Reply
    • Mike.Dietrich says:
      January 21, 2018 at 09:35

      Rudolfo,

      I dropped you an email – and one thing to consider: there’s no reason not to apply BPs on SE2. I know that some MOS note recommended this but I discussed this internally and I think to remember that the reason recommending this has been fixed ages ago.

      Regarding “adaptive” and “dynamic sampling” see my email please.

      Cheers, Mike

      Reply
  5. JB says:
    February 5, 2018 at 18:09

    It says a lot that Oracle’s own EBusiness Suite mandates turning all adaptive features off.

    Reply
    • Mike.Dietrich says:
      February 7, 2018 at 11:57

      Well, they may not have heard about the fixes yet 😉
      And Peoplesoft runs very well when you applied them 🙂

      Plus in 12.2 EBS will run with the defaults as far as I expect 😉

      Cheers,
      Mike

      Reply
  6. JB says:
    February 8, 2018 at 21:00

    EBS 12.2 with its editioning complications (needs 30 odd patches to run) is not yet certified on RDBMS 12.2.
    Initially they did not recommend turning the adaptive stuff off. But as more and more customers started using EBS 12.2 – it became apparent that the plan directives do not give consistent performance. Then the ATG team made the recommendation to turn it off.

    There is a custom app we had which actually had ORA- errors with adaptive enabled. Maybe I can request the DBA to try the new fixes.

    Reply
  7. Gerrit Haase says:
    February 12, 2018 at 18:19

    In the above “What is your task now?” section i.e. to the “It’s very simple” list, please add one step more which is mentioned in the MOS document as last step after the restart of the database:

    6. Connect to SYS (as sysdba) using sqlplus and run the script execstat.sql:
    connect / as sysdba
    @?/rdbms/admin/execstat.sql

    I nearly missed this step.
    Regards

    Reply
  8. Diana says:
    March 15, 2018 at 20:32

    Hi Mike,

    Thanks for this post. I deployed the Jan 2018 BP for 12.1.0.2.
    We had no entry in the spfile for optimizer_adaptive_features, however with a show parameter the value was set to TRUE.
    After the patch, the 2 new parameters showed up as well with a value of TRUE.
    I enabled the fixes, as per the document and restarted the database:
    SQL> alter system set “_fix_control”=’26664361:7′,’16732417:1′,’20243268:1′ scope=spfile;
    System altered.
    SQL> alter system set optimizer_adaptive_statistics=FALSE scope =spfile;

    Do I need to set optimizer_adaptive_features to FALSE explicitly?
    So the end result is :
    optimizer_adaptive_features=FALSE, optimizer_adaptive_statistics=FALSE , optimizer_adaptive_plans=TRUE
    It is not clear in the note.
    Thank you
    Diana

    Reply
    • Mike.Dietrich says:
      March 16, 2018 at 18:11

      Diana,

      no – but you should set optimizer_adaptive_plans=true/false and *IMPORTANT* optimizer_adaptive_statistics=false *explicitly* in your init.ora/spfile.
      If it is just defaulting it seems to have different behavior than if explicitly set.

      No need to touch optimizer_adaptive_features.

      Thanks
      Mike

      Reply
      • Diana says:
        March 20, 2018 at 04:18

        Thank you Mike! That makes sense!

        Reply
        • Diana says:
          March 20, 2018 at 17:14

          Hi Mike,

          After the patch deployment, and the init.ora parameter modification, as per your recommendation:
          leave optimizer_adaptive_features as is (TRUE), optimizer_adaptive_statistics=false , optimizer_adaptive_plans=true,
          I still notice that dynamic sampling occurs and SQL Plan Directives are in use. Not all the time, but they are used.
          If we want to totally avoid the usage of directives, then should I delete all the existing directives that were created prior to the patch? Are these directives used, and thus dynamic stats gathered, because the directives are there, and the optimizer is taking advantage of them?

          Thank you for your time!
          Diana

          Reply
          • Mike.Dietrich says:
            March 20, 2018 at 21:39

            Diana,

            please remove optimizer_adaptive_features from your spfile.
            Just have:
            *._fix_control=’26664361:7′,’16732417:1′,’20243268:1′
            *.optimizer_adaptive_plans=true
            *.optimizer_adpative_statistics=false
            and give it a try again.

            If that doesn’t solve it please drop me an email with more details – I’m will check it.
            An SR in addition would be very helpful in case the problem persists.

            Thanks in advance and sorry for all the inconvenience!

            Mike

  9. Diana says:
    March 22, 2018 at 18:56

    Hi Mike,
    I thought to give you an update on this. I have opened an SR with Oracle, and it seems we are hitting a bug:

    Patch 27626925: OPTIMIZER ADAPTIVE STATS DEFAULT FALSE NOT HONORED WHEN ENABLED IN OCT OR JAN BP

    I am planning on deploying shortly the Patch, and will let you know if it fixes the problem (SQL Plan Directives still in use/created)

    Thank you for the time you took to answer my questions!

    Diana

    Reply
    • Mike.Dietrich says:
      April 1, 2018 at 18:59

      Hi Diana,

      thanks for taking the time to update me. I see the bug – and I read several blog posts about it.
      Actually it should work as expected when you set explicitly optimizer_adaptive_statistics=false in your SPFILE and restart the database.

      Thanks
      Mike

      Reply
  10. Vimal says:
    May 1, 2019 at 02:46

    Hi Mike,
    Recently we upgraded from 11.2.0.4 to 12.1.0.2.181016 (12.1.0.2 +OCT PSU).There are major performance issues after upgrading.
    Do we still need to take care of the above recommendation? As I can see patches mentioned by you already included.
    I make optimizer_adaptive_features false So It makes other parameters false as well.
    Before Change parameter Values:—–
    optimizer_adaptive_features TRUE
    optimizer_adaptive_plans TRUE
    optimizer_adaptive_reporting_only FALSE
    optimizer_adaptive_statisticsTRUE
    After change:—
    optimizer_adaptive_features FALSE
    optimizer_adaptive_plans FALSE
    optimizer_adaptive_reporting_only FALSE
    optimizer_adaptive_statistics FALSE
    optimizer_capture_sql_plan_baselines FALSE

    Parameter _fix_control is blank.
    Please let me what is the recommended setting for 12.1.0.2+OCT PSU.

    Reply
    • Mike.Dietrich says:
      May 6, 2019 at 11:19

      Hi Vimal,

      you still need to follow the advice of the notes (or as mentioned in the blog post) to enable the parameters and the changes.
      And there are a lot of changes from 11.2.0.4 to 12.1.0.2 – hence, detailed testing is really highly recommended.
      You may please work and go forward together with Oracle Support.

      Cheers,
      Mike

      Reply
  11. Mano says:
    August 26, 2019 at 19:35

    Hi Mike

    We did upgrade 11.2.0.0 to 12.2.0 , still didn’t change compatible parameter. Since, in 12c optimizer_adaptive_features is deprecated and has been divided into 1. optimizer_adaptive_plans= True and 2. optimizer_adaptive_statistics= FALSE. We want to change only optimizer_adaptive_plans to FALSE, Can you please tell me is there a dependency on optimizer_adaptive_statistics, will there be any impact at application level.

    Regards
    Mano

    Reply
    • Mike.Dietrich says:
      September 2, 2019 at 18:02

      Hi Mano,

      you should:
      1. Remove optimizer_adaptive_features
      2. Set optimizer_adaptive_statistics=false
      3. Set optimizer_adaptive_plans=true if you’d like to have this part enabled

      Cheers,
      Mike

      Reply
  1. Infinite parse loops with ADG, adaptive statistics and Oct 2017 BP – Oracle, Exadata, Multitenant and related
    December 25, 2017

    […] indeed been included in the October BP. You can find the details in document 22652097.8 , or see here. According to this however – the whole change should be disabled by default and only be […]

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mike Dietrich
Oracle Senior Director of Product Management for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 8,090 other subscribers

Follow me on Twitter