Re: The exact difference between SQL profile and baselines in Oracle 11

From: TheBoss <TheBoss_at_invalid.nl>
Date: 10 Oct 2011 22:01:37 GMT
Message-ID: <Xns9F7B472434ATheBossUsenet_at_194.109.133.133>

TheBoss <TheBoss_at_invalid.nl> wrote in news:Xns9F7AF386D56EATheBossUsenet_at_194.109.133.133:

> Mladen Gogala <gogala.mladen_at_gmail.com> wrote in
> news:pan.2011.10.10.18.35.46_at_gmail.com:
>

>> On Mon, 10 Oct 2011 19:09:08 +0200, Robert Klemme wrote:
>> 
>>> I think a baseline contains the data on which profile creation is
>>> based: 
>> 
>> Baselines are plans. Profiles contain hints and therefore are used to
>> influence plans. My problem is what happens when I have both. Will 
>> profiles influence the plans determined by baselines? What is the
>> exact mechanism? Oracle has several mechanisms covering roughly the
>> same area: outlines, profiles and baselines. I am trying to develop
>> test cases to see what happens in the different situations, but it's
>> slow. What I'm asking for is if somebody else has done that research.
>> 

>
> Mladen,
>
> Oracle's Optimizer Development team has written a very interesting
> 4-part serie of articles on their blog on the subject of SQL Plan
> Management:
>

<...>
>
> Parts 3 and 4 have examples of Baselines and Profiles working together
> for optimal results. As you also mentioned Outlines:
> They fairly recently wrote a blog article on why you should migrate
> them (especially if you're on 11g) to Baselines and how to do that:
>
> =====
> http://blogs.oracle.com/optimizer/entry/how_do_i_migrate_stored
> =====
>
> Note:
> if you don't mind reading articles from bottom to top, you can use
> following link to get all articles mentioned above in one go:
>
> =====
> http://blogs.oracle.com/optimizer/tags/sql_plan_management
> =====
>
> HTH
>
> Cheers!
>

Also have a look at these 2 articles on Kerry Osborne's blog, including an interesting discussion between Kerry and Jonathan:

 http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my- outline-profile-baseline/

 http://kerryosborne.oracle-guy.com/2010/02/single-hint-sql-profiles/

-- 
Jeroen

Received on Mon Oct 10 2011 - 17:01:37 CDT