Oracle Scratchpad

August 2, 2016

Adaptive mayhem

Filed under: 12c,Oracle — Jonathan Lewis @ 4:29 pm BST Aug 2,2016

So you run a query and it gives you a plan with a note that says “This is an adaptive plan”.

So you run it again and the plan changes,  with a note that says “Statistics feedback used for this statement”

So you pause to think for a bit, then run the query again and the plan changes, with a note that says “One SQL Directive used, dynamic statistics used”. You waited too long and the internal re-optimization hints (v$sql_reoptimization_hints) got flushed down into an SQL directive (dba_sql_plan_directives).

So you decide to think about it the following morning when you’re feeling bright and fresh, and when you run it you get another plan because overnight the automatic stats job gathered stats on the critical table and created a column group (dba_stat_extensions) that was indicated by the (now “redundant”) directive.

Happy optimisation!

See also:

Update 1:

This just in from fellow Oak Table member Stefan Koehler:If the automatic stats collection task doesn’t finish inside its window that you may get unlucky and find that Oracle has created extended stats on a table, failed to complete the stats collection, and dropped the extended stats. Bug 19450314 mentioned above avoids the problems of unnecessary invalidation when the extended stats are added and collected – it doesn’t deal with the problem if the stats are dropped.

Update 2(Sep 2016):

Fresh news from OpenWorld 2016 on 12.2, courtesy of DBI Services’ Franck Pachot: The parameter contolling adaptive optimisation has been split into two parts, one for the adaptive plans the other for adaptive statistics – so you can officially switch of the bit that’s more likely to cause unexpected side effects while leaving the bit that’s more likely to be beneficial active.

Update 3 (Nov 2016):

The latest news from Stefan Koelher on Bug 19450314 (see update 1 above) is that the unfinished bits of the fix for this bug are now known as bug 21418655. “Not done were the following: MODIFY, RENAME and DROP column (including work on Drop_Extended_Stats).”

Update 4 (Dec 2016)

And now a potential threat (about column groups in general) described in a series of three blog posts from Magnus Johansson. With reference to a relevant MoS Doc Id and patch.

Update 5 (May 2017)

By now it’s probably fairly common knowledge that 12.2 splits the adaptive features into two area (adaptive execution and adapative statistics) that can be enabled or disabled separately. (A good source of information with several related links is here in Mike Dietrich’s blog). But if you’ve already acquired a load of column groups that you don’t want you might want to check Mauro Pagano’s blog for a simple script to eliminate them.

Update 6 (Jan 2018)

Adding insult to injury – if Oracle has silently created some column groups (or other extended stats) on an unpatched version of 12.1 then you’ll find that you can’t rename any of the referenced columns, and will raise Oracle error ORA-54032 if you try.  The error is actually about virtual columns (“column to be renamed is used in a virtual column expression”) rather than extended stats explicitly, but interestingly wouldn’t apply if you had created a function-based index to achieve the same effect. This is MoS bug 21070487 : CAN’T RENAME COL WHEN REFERENCED IN VC’S EXPR; CAN WHEN IN EXPR FOR INDEX (marked as fixed in 12.2).


  1. Oracle optimizes itself.
    Don’t get me started on 12c adaptive features.

    Comment by piontekdd — August 2, 2016 @ 4:32 pm BST Aug 2,2016 | Reply

  2. Turn off adaptive optimization.

    Comment by Amir Hameed — August 2, 2016 @ 6:54 pm BST Aug 2,2016 | Reply

  3. I second that Amir :-)

    Comment by Fairlie Rego — August 3, 2016 @ 6:00 am BST Aug 3,2016 | Reply

  4. I must admit my first experience with adaptive optimization was actually a happy one.
    A heavy-on-CPU query that is impossible to tune (3rd party, no source, dynamic, yadda-yadda) actually improved markedly once I got the db into 12c and left the adaptive optimization on.
    What I’d like is the option to “freeze” the current plans and turn adaptive off, but not sure if that is practically possible.
    What is worrying me a lot more than this is the CDB/PDB thing. Connections via sqlnet to PDBs are problematic to say the least. Particularly from 11g clients…
    I think the whole she-bang of “become like Sybase/MSSQL was handled a bit in a rush and without full awareness of all the implications. Still, some good progress in some areas I’ve seen so far in

    Comment by Noons — August 3, 2016 @ 7:57 am BST Aug 3,2016 | Reply

    • Noons,

      The really annoying thing about adaptive optimization is that sometimes it works really well, adapative execution plans in particular (which, I guess, is what appeared in your case) – the optimizer predicts where a problem might appear and decides on the necessary evasive action in advance. Even then the amount of work the optimizer does to derive inflexion points can be far from cost-effective.

      As always we end up in a place where 98% of the time nothing changes, 1% it changes for the better and we don’t usually notice, and 1% it changes for the worse and we complain loudly.

      Comment by Jonathan Lewis — August 3, 2016 @ 12:33 pm BST Aug 3,2016 | Reply

  5. Our experience is that, in a very busy RAC system, adaptive optimization can run into many issues. We actually had to disable it for one such database. For databases with moderate load profiles, we are able to live with it.

    Comment by VK — August 3, 2016 @ 5:59 pm BST Aug 3,2016 | Reply

  6. Easy to fix … don’t think so much !

    Comment by Geert — August 3, 2016 @ 6:06 pm BST Aug 3,2016 | Reply

  7. I wish there was an “optimizer_mode” setting which targeted stability over aggressive re-optimisation. Of course, you can just turn off all the adaptive features…. but there are many.

    Comment by Dom Brooks — August 5, 2016 @ 9:38 am BST Aug 5,2016 | Reply

  8. The funny part is that “SQL directives” override SPM, i.e. SQL Plan Baselines one creates in the hope of fixing and freezing the plan for good.
    Haven’t tested it a lot but my guess is that SQL directives can override outlines, hints, sql patches, perhaps manually set statistics.

    Comment by laimisndLaimis — August 9, 2016 @ 7:19 am BST Aug 9,2016 | Reply

    • Do you have a link to a demonstration of directives over-riding baselines ? I haven’t tested it and it does seem a little unlikely (though I can imagine the directive still driving sampling that is used to produce a plan that becomes a new, but not accepted, basline) although I have come across a very small number of Baselines that weren’t complete specifictions for a plan and therefore allowed some room for change.

      I would certainly expect SQL Directives to have the ability to override (incomplete) hints, (abused) SQL patches and manually sets statistics. Anything short of a completely specificied plan could be modified by dynamic sampling, so I think an outline would be much more likely to be affected by an SQL directive than an SQL Plan Baseline since outlines tend to be less well specified.

      Comment by Jonathan Lewis — August 15, 2016 @ 4:08 pm BST Aug 15,2016 | Reply

      • Will see if I can dig it out. That happened on 12c ( I believe). It could have been an isolated case of incomplete baseline like you described. For sure the baseline was created from a cursor cache – this is the technique I prefer for a quick tuning. The plan was still the “bad” one till I deleted directives on the corresponding objects.
        Here is some infos I’ve got from Oracle:

        Comment by laimisnd — August 17, 2016 @ 6:45 am BST Aug 17,2016 | Reply

      • ok, can’t dig out the exact case any more. What was done however is this: this particular application would normally generate a number of v$sql entries with different plan_hash_values. Some of phv’s would be good enough. Then I would execute dbms_spm.load_plans_from_cursor_cache, for example:

        var res number
        exec :res := dbms_spm.load_plans_from_cursor_cache( sql_id => 'gvuc1cdpawyyg', plan_hash_value => 4293452016); 
        print res
        for m in (select address , hash_value from v$sqlarea where sql_id = 'gvuc1cdpawyyg') 
        end loop;

        and verify if the baseline is taken:

        select child_number, executions, plan_hash_value, last_load_time, round(elapsed_time/(executions+0.001)/1000) avg_exec_time_ms, sql_id, SQL_PLAN_BASELINE,sql_profile,sql_patch, elapsed_time, s.* 
        from v$sql s 
        where sql_id='gvuc1cdpawyyg' 
        order by 4 desc;

        Now, on that particular case the SQL_PLAN_BASELINE has been taken by CBO, like it should. However, the plan steps were still wrong. dbms_xplan.display_cursor would show that sql directives have been used.

        After deleting those directives the good plan was finally accepted.

        Now if that was a one time glitch or a pre-programmed feature I wouldn’t tell. I suppose that the easiest way to implement stored plans is by using hints and letting optimizer run it’s due course: hints would cut off some decision tree branches – or if no branch qualifies then hint is deemed invalid and discarded.

        Comment by laimisnd — August 17, 2016 @ 7:48 am BST Aug 17,2016 | Reply

        • Interesting.

          In principle a baseline should define a path 100%, but there may be cases where the code is not complete so a baseline set of hints could be followed but still give a change of plan because the dynamic sampling produced stats that affected how those hints were used. (Seems low probability, though).

          One thing I inferred from Nigel’s blog note (specrfically his reply to your comment) is that if you have multiple accepted baselines for the same query then the one chosen at any particularly moment could be dictated by whatever stats came out of the dynamic sampling.

          Comment by Jonathan Lewis — August 17, 2016 @ 10:44 am BST Aug 17,2016

        • > In principle a baseline should define a path 100%, but there may be cases where the code is not complete so a baseline set of hints could be followed but still give a change of plan
          In that case the baseline plan *should* be rejected because the phv wouldn’t match and so wouldn’t be reported as being used. The scenario of there being multiple accepted plans is the only one which seems to make sense, on the face of it.

          Comment by Dom Brooks — August 17, 2016 @ 12:14 pm BST Aug 17,2016

        • here is more to confirm the view that SPM should have the last say when profiles and sql patches are present:
          FAQ: SQL Plan Management (SPM) Frequently Asked Questions (Doc ID 1524658.1)
          “If a baseline is present for a particular SQL ID, then a profile or a SQL_PATCH will only have an effect if the plan generated by the profile or the SQL_PATCH is Enabled and Accepted within the baseline.”

          Following this logic the same should be valid for SQL directives too. Conceptually, hints (which constitute profiles and sql patches) can be viewed as extra statistics.
          As to the question why SQL baseline failed to enforce a plan in my particular case – it could have been a simple glitch. However, Oracle provided a hint what could have gone wrong by acknowledging that “SQL Directives have been used”. It was not a big deal to fix this one case.

          Comment by laimisnd — August 17, 2016 @ 2:05 pm BST Aug 17,2016

        • There is at least one corner case here:

          ” With a SPM baseline there is verification of whether the plan you want to force has been reproduced or not. ” (Doc ID 1524658.1)
          “A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. ” ( )

          what if optimizer was not able to reproduce the plan ? It can happen (also unlikely but can) if for example CBO has reached max permutations limit. Oracle won’t fail, this is not in the spirit of SQL databases. Any found plan would do, obviously.

          Comment by laimisnd — August 17, 2016 @ 2:30 pm BST Aug 17,2016

      • I tried to see what happens inside CBO. According to the 10053 trace, if one assumes that the order of order the trace file lines correspond to CBO execution order, then SPD/SPM kicks of as the last batch of CBO activities.
        The following trace SPD sections are at the end of the trace file:

        SPD: BEGIN context at statement level

        ******* UNPARSED QUERY IS *******
        SELECT /*+ LEADING (“T4” “T1” “T2” “T3”) FULL (“T4”) USE_HASH (“T3”) FULL (“T3”) USE_HASH (“T2”) FULL (“T2”) USE_HASH (“T1”) FULL (“T1”) */ …

        Permutations for Starting Table :0
        Join order[1]: T4[T4]#0 T1[T1]#1 T2[T2]#2 T3[T3]#3

        The join order above is the join order dictated by SPM hints.

        However, CBO does run all non SPM governed permutations at the beginning of the trace file.
        Why that is done as the initial activity is not exactly clear to me.

        Comment by laimisnd — August 29, 2016 @ 12:48 pm BST Aug 29,2016 | Reply

  9. […] Here are quite interesting thoughts of Jonathan Lewis […]

    Pingback by SQL directives beat SPM (or not?) | Laimis Oracle blog — September 1, 2016 @ 6:50 am BST Sep 1,2016 | Reply

  10. Jonathan,
    I managed to discuss planned changes with CBO team at OOW.
    I have done a quick blog-post about them here:
    Summarizing points I haven’t seen elsewhere:
    The splitting of optimizer_adaptive_features parameter will be back-ported via a patch to 12.1 (maybe with the same defaults).
    With default settings SQL Plan Directives will still be created, but will not automatically cause dynamic sampling or creation of column groups (is this is how automatic creation of column groups currently works?).
    New mechanism for persistence of dynamic sampling query results. No longer uses result cache. Information is available to all RAC nodes and persists across instance restarts. These results are automatically invalidated when sufficient changes made to base tables (somewhat similar to stale statistics with dbms_stats).

    Comment by hkpatora — September 25, 2016 @ 4:17 am BST Sep 25,2016 | Reply

    • From Patrick Hurley, changes now documented in MOS note 2187449.1.

      Comment by hkpatora — October 1, 2016 @ 4:19 am BST Oct 1,2016 | Reply

      • Patrick,

        Thanks for that.
        I think the most significant part of that note is this:

          We recommend that upgrades to 12.1 adopt the 12.2 defaults. This may be done by applying the following patches:

            Patch 22652097 (Not yet available) splits the parameter optimizer_adaptive_features into two, as above, and disables adaptive statistics.
            Patch 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.

        Comment by Jonathan Lewis — October 1, 2016 @ 9:07 am BST Oct 1,2016 | Reply

        • Seems quite significant and even extraordinary that optimizer_adaptive_statistics should default to FALSE in 12.2.

          Comment by Dom Brooks — October 3, 2016 @ 11:58 am BST Oct 3,2016

  11. […] Jonathan Lewis tweeted Adaptive Mayhem […]

    Pingback by Winter Analytics | Oracle Business Intelligence — November 7, 2016 @ 7:39 pm GMT Nov 7,2016 | Reply

  12. […] 12. Adaptive Mayhem […]

    Pingback by Best of OBI 2016 | Oracle Business Intelligence — December 31, 2016 @ 5:36 pm GMT Dec 31,2016 | Reply

  13. […] Jonathan Lewis tweeted Adaptive Mayhem […]

    Pingback by Winter Analytics – OBIEE News — January 22, 2017 @ 12:50 pm GMT Jan 22,2017 | Reply

  14. […] thread on Oracle-l on hit of IO for an EBS environment due to extended statistics.  There were links in the conversation to Jonathan Lewis’ blog that bring you to some incredibly interesting […]

    Pingback by Why the DBA is Necessary to the Cloud- Part I - DBA Kevlar — March 2, 2017 @ 8:12 pm GMT Mar 2,2017 | Reply

  15. […] Adaptive Mayhem (Aug 2016): The anguish of 12.1 and adaptive statistics. […]

    Pingback by Column Group Catalog | Oracle Scratchpad — September 27, 2018 @ 5:16 pm BST Sep 27,2018 | Reply

  16. […] opening plan above) the table estimate was signficantly greater than the index estimate. This was a side effect of adaptive statistics: the low table estimate was due to the basic “multiply separate selectivities”; but the […]

    Pingback by Strange Estimates. | Oracle Scratchpad — October 31, 2019 @ 8:37 am GMT Oct 31,2019 | Reply

  17. […] final warning arrives with 12c. If you have all the adaptive optimizer options enabled the optimizer will keep a look out for […]

    Pingback by Optimizer Tip | Oracle Scratchpad — September 20, 2021 @ 9:04 am BST Sep 20,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by

%d bloggers like this: