Re: Do missing binds prevent creation of baseline?
Date: Tue, 16 Apr 2013 23:51:02 -0500
Message-Id: <F0651AE8-809C-49E5-B504-7DC340F4B8D1_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-lReceived on Wed Apr 17 2013 - 06:51:02 CEST