Re: Do missing binds prevent creation of baseline?

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Thu, 18 Apr 2013 18:24:00 -0500
Message-Id: <F7C74F3E-7A5B-43CE-B313-306AC15A7377_at_enkitec.com>

Hi Mohamed,
  My pseudo code was for a "a very simple case of a single accepted baseline and capture turned off" so with only one baseline there is no trying to decide which to use or retrying other baselines. But I think your question is more around the mechanics of how plans are generated. In 11g the plans are indeed generated by the optimizer using the hints contained in the specified baseline so yes there is some additional parse overhead in cases where the initial hard parse does not come up with a plan in an enabled/accepted baseline.

If you look in v$mystat you'll see the hard parses increment by 1 when a matching plan is arrived at on the first pass and 3 hard parses when a single baseline is not reproducible.

• Match first pass parse count (total) 7 parse count (hard) 1
• Non-reproducible plan (dropped index) parse time elapsed 2 parse count (total) 9 parse count (hard) 3 parse count (failures) 2

And a 10053 trace shows:

SPM: plan reproducibility round 1 (plan outline + session OFE) SPM: failed to reproduce the plan using the following info:

  parse_schema name        : SYS
  plan_baseline signature  : 2174518826393105558
  plan_baseline plan_id    : 1416105523
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
    hint num  4 len  8 text: ALL_ROWS
    hint num  5 len 22 text: OUTLINE_LEAF(_at_"SEL$1")
    hint num  6 len 53 text: INDEX_RS_ASC(_at_"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))

SPM: generated non-matching plan:

...

SPM: plan reproducibility round 2 (hinted OFE only) SPM: failed to reproduce the plan using the following info:

  parse_schema name        : SYS
  plan_baseline signature  : 2174518826393105558
  plan_baseline plan_id    : 1416105523
  plan_baseline hintset    :
    hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

SPM: generated non-matching plan:

...

SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 1429740808

So as Dominic said, there are two additional hard parses when the original try doesn't work - and there is no doubt that that causes some additional overhead.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
twitter: https://twitter.com/KerryOracleGuy

On Apr 18, 2013, at 1:50 AM, Mohamed Houri wrote:

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 19 2013 - 01:24:00 CEST