How Adaptive Plans work with SQL Plan Baselines?

Disclaimer: after writing this post (but before publishing it) I have seen that other people already blogged about it, so I am ashamed of publishing it anyway… but that’s blogger’s life 🙂

Wednesday I have got a nice question after my presentation about Adaptive Features at the DOAG16 conference:

What happens when you load an adaptive plan in a SQL Plan Baseline?
Does it load only the final plan or does it load the whole plan including the inactive operations? Will the plan be evaluated again using the inflection point?

I have decided to do some tests in order to give the best possible answer. I did not spend the time to rethink about producing an adaptive plan. Tim Hall already did an excellent test case to create and alter an adaptive plan in his blog, so I have reused massively most of its code. Thanks Tim :-).

I will not post all the code (please find it in Tim’s post), I will go straight to the plans.

First: I have an adaptive plan that resolves to NESTED LOOPS:

Second: I load the plan (lazy way: using baseline capture at session level)

Third: re-run the statement and check the plan

It does not look adaptive, but I can also check from the function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE:

Again, despite in the Note section it says it is adaptive, it does not look like an adaptive plan.

Can I trust this information? Of course I did not and tried to check the plan with and without baseline after changing the rows to force a plan switch to HJ (again taking Tim’s example):

After changing the rows:

  • when I do not use the baseline, the plan resolves to HASH JOIN
  • when I use it, the baseline forces to NESTED LOOPS.

So the plan in the baseline is not adaptive and it forces to what has been loaded. Is it the final plan or the original one? I have to capture it again to see if a new baseline appears:

A new baseline does not appear, so it looks that the original plan is considered by the capture process and not the resolved one! To be 100% sure, let’s try to drop the existing one and redo the test:

So, despite the fact that I have an adaptive plan that switches from NL to HJ, only the NESTED LOOPS operations are captured in the baseline, I can infer the only the original plan is loaded as SQL Plan Baseline.

References:

Be Sociable, Share!
The following two tabs change content below.

Ludovico

Oracle ACE and Senior Consultant at Trivadis SA
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Senior Database Specialist for Trivadis, Switzerland.

Latest posts by Ludovico (see all)

3 thoughts on “How Adaptive Plans work with SQL Plan Baselines?

  1. Pingback: How Adaptive Plans work with SQL Plan Baselines? - Ludovico Caldara - Blogs - triBLOG

  2. Salut Ludo

    J’espère que vous allez très bien.

    Thanks for this post. Indeed the automatic capture of sql plan baseline captures only the default plan.
    This can be confirmed by getting the stored outline of captured SPM baseline where you can see that the NL join is used.

    select
    substr(extractvalue(value(d), ‘/hint’), 1, 100) as outline_hints
    from
    xmltable(‘/*/outline_data/hint’
    passing (
    select
    xmltype(other_xml) as xmlval
    from
    sys.sqlobj$plan
    where
    signature in (select
    signature
    from dba_sql_plan_baselines
    where plan_name = ‘SQL_PLAN_f7xzs75fcmngv55a25f73’
    )
    and other_xml is not null
    )
    ) d;

    OUTLINE_HINTS
    ———————————————————-
    NLJ_BATCHING(@”SEL$58A6D7F6″ “B”@”SEL$1″)
    USE_NL(@”SEL$58A6D7F6” “B”@”SEL$1″)
    LEADING(@”SEL$58A6D7F6” “A”@”SEL$1” “B”@”SEL$1″)
    INDEX(@”SEL$58A6D7F6” “B”@”SEL$1” (“TAB2″.”TAB1_ID”))
    BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$58A6D7F6″ “A”@”SEL$1″)
    INDEX_RS_ASC(@”SEL$58A6D7F6” “A”@”SEL$1” (“TAB1″.”CODE”))
    OUTLINE(@”SEL$1″)
    OUTLINE(@”SEL$2″)
    MERGE(@”SEL$1″)
    OUTLINE_LEAF(@”SEL$58A6D7F6″)
    ALL_ROWS
    DB_VERSION(‘12.1.0.1’)
    OPTIMIZER_FEATURES_ENABLE(‘12.1.0.1’)
    IGNORE_OPTIM_EMBEDDED_HINTS

    There is also a column named adaptive in dba_sql_plan_baselines which indicates that the SPM plan is not adaptive

    The 10053 trace file shows also interesting information:

    SPM: cost-based plan found in the plan baseline, planId = 1436704627
    SPM: cost-based plan successfully matched, planId = 1436704627
    SPM: match found but the plan is dynamic

    Oracle consider the first plan (NL) as being one of the CBO generated plan even though it is not the final one used during query execution (HJ). In this case Oracle considers that the CBO default (NL) plan matches the SPM(NL) plan and, thereby,
    the CBO (or the SPM plan no matter here because they are identical in the eyes of the CBO) is used.

    When the CBO come up with an execution plan that is not in the SPM baseline it will insert this CBO plan in the SPM baseline for future evolution. You can easily see that, in the case where Oracle resolved to a dynamic HASH Join execution plan and end up by using the SPM (NL) plan, the CBO dynamic HASH join plan has not been inserted into the SPM baseline. This is another proof that Oracle considers both plans (default and the alternative one) to have been produced by the CBO and uses one of them provided they matches the planId (phv2) of the SPM plan.

    Anyway thanks for this information : only the default plan is captured automatically into the SPM baseline when the plan is adaptive

    Best regards
    Mohamed Houri

Leave a Reply

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