Re: Do missing binds prevent creation of baseline?

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Thu, 18 Apr 2013 08:50:57 +0200
Message-ID: <CAJu8R6jVLsEwCP+V_GRpNJ_vobf=gxUB68_CvcmUwdhAU+3p2w_at_mail.gmail.com>



Hi Kerry,
I have been looking around to see from where youíve got the SPM selection algorithm youíve posted (particularly the code marked below in this color)

Parse the statement without regard to the baseline IF the resulting plan hash matches the plan hash stored with the baseline   use it
ELSE
  apply hints from baseline and re-optimize   IF the resulting plan hash matches the plan hash stored with the baseline     use it
  ELSE
    use the original plan determined by optimizer without hints   END IF
END IF But I am still unsuccessful to find the corresponding documentation or a blog article that supports this. However, I can find many references (including Oracle corporation) stipulating the following algorithm

Parse the statement without regard to the baseline IF the resulting plan hash matches the plan hash stored with the baseline

   use it
ELSE
    use the best costed SPM plan that is (Accepted/Enabled/Reproducible).

    *IF we canít reproduce the plan in the SPM baselines (index dropped for example) *

  • THEN*
  • The other (Accepted/Enabled/Reproducible) plans in the SPM will compete and the best*
  • One will be used.*
  • IF no (Accepted/Enabled/Reproducible) plan is found into the SPM baseline*

  THEN      Use the plan that CBO comes up with at hard parse time

    END IF;  END IF  This is backed by the Oracle documentation

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABBECCH

I have also summarized this SPM Plan selection process in the following blog article (see the diagram taken from Oracle doc.):

http://hourim.wordpress.com/2013/04/01/sql-plan-mangementspm-and-adaptive-cursor-sharingacs-my-resume/

May be that the algorithm youíve posted fits cases where *both* SPM and SQL Profiles are mixed. I have never been confronted to such kind of situation. The conclusion I came to is that SPM do not interfere in the work of the CBO. The SPM ensure that the plan generated by the CBO will be used only if it exists in the SPM baseline and is enabled, accepted and reproducible. However, when no plan in the SPM is reproducible then the CBO plan will be used because there is nothing in the SPM to constrain it.

If your algorithm is correct i.e. (*apply hints from baseline and re-optimize*) then the SPM will certainly generate a performance impact somewhere because it will re-optimise an already optimised plan.

Best regards

Mohamed Houri

2013/4/17 Kerry Osborne <kerry.osborne_at_enkitec.com>

> Yes - Maria's words are chosen carefully. Baselines are designed to limit
> the optimizer to a set of accepted plans. Unfortunately, the 11g
> implementation is still hint based. So the optimizer has to determine a
> plan for the statement (after applying all the hints stored in the baseline
> if necessary). As you might imagine, there are occasional issues as a
> result. Like if there is an index that gets dropped or for some reason the
> hints are not specific enough to narrow the optimizers choices to a single
> plan. But in almost all cases it works very well. And it is the same basic
> approach as profiles and patches.
>
> The algorithm looks something like this (for a very simple case of a
> single accepted baseline and capture turned off).
>
> Parse the statement without regard to the baseline
> IF the resulting plan hash matches the plan hash stored with the baseline
> use it
> ELSE
> apply hints from baseline and re-optimize
> IF the resulting plan hash matches the plan hash stored with the baseline
> use it
> ELSE
> use the original plan determined by optimizer without hints
> END IF
> END IF
>
> This is why Maria says (in the blog article you referenced) that if an
> accepted plan can not be reproduced, "the baseline is discarded". When that
> happens, the optimizer uses a plan generated by the optimizer with no
> hints. This is in contrast to SQL Profiles, which have no concept of the
> plan they are trying to reproduce. With Profiles, the hints are applied and
> whatever plan the optimizer comes up with them, that's the plan that gets
> used.
>
> My views are probably a little skewed from the norm as I have definitely
> abused SQL Profiles for something they weren't originally intended for and
> I still tend to favor them for their flexibility. If you want more info, I
> have done several talks about Profiles/Baselines/Patches (and hope to do
> one at OOW this year focused on the changes in 12c which may cause to
> change my preference to baselines). Here's a link to the slides from the
> most recent version that I did last year:
> http://kerryosborne.oracle-guy.com/papers/controlling_execution_plans_2012.zip.
> If you are a glutton for punishment and want even more reading material,
> there is a chapter in Karen Morton's Pro Oracle SQL and another in Chris
> Antognini's book. I'm sure there are others as well. And several bloggers
> have written about them too, Randolf Geist for example and Dominic, who's
> posts on the topic I hadn't run across before this thread. (nice job by the
> way Dominic)
>
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com
> twitter: https://twitter.com/KerryOracleGuy
>
>
>
>
>
>
>
>
> On Apr 16, 2013, at 3:18 PM, Rich Jesse wrote:
>
> > Hey Kerry,
> >
> >> 1. In 11g, baselines use the same hint based mechanism as profiles and
> >> patches. That is to say that there is no guarantee with baselines that
> the
> >> plan will reproduced, just as with the other hint based options. There
> is
> >> not a true stored plan with baselines, at least as of 11g. I think it
> might
> >> be a coming attraction of some future release.
> >
> > Hmmm...perhaps I am misunderstanding this blog post (commented by
> Dominic!):
> >
> > https://blogs.oracle.com/optimizer/entry/what_is_the_different_between
> >
> > So "plan" in the context of the article's reference to "SQL Plan
> Baseline"
> > is not really the SQL execution plan viewed in V$SQL_PLAN? And if the
> > optimizer is only able to choose between the plans available via
> baselines,
> > wouldn't that absolutely prevent any alteration of the execution path,
> > regardless of other changes (e.g. missing index, structure changes,
> etc.)?
> >
> > Poking around the data dictionary, are you referring to the COMP_DATA
> column
> > of SYS.SYSOBJ$DATA? I am unable to find a reference to a true stored
> > plan... :\
> >
> >> So I often fall back to set from v$sql_bind_capture if I can't find
> what I
> >> want in other_xml.
> >
> > ...which is missing the binds of the columns to be modified via this
> UPDATE.
> >
> >> 3. Not updating 1.8M records per night is probably the the correct fix.
> :)
> >
> > Whew! I think I got something right today! ;) Hopefully, between you
> and
> > Dominic, I'm learning something about 11g...
> >
> > Thanks!
> > Rich
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 18 2013 - 08:50:57 CEST

Original text of this message