Re: The exact difference between SQL profile and baselines in Oracle 11
Date: 10 Oct 2011 21:56:21 GMT
Message-ID: <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:
http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4 _creating_sql_plan_baselines
http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_2_of_4 _spm_aware_optimizer
http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_3_of_4 _evolving_sql_plan_baselines_1
http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4 _user_interfaces_and_other_features
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!
-- JeroenReceived on Mon Oct 10 2011 - 16:56:21 CDT