I must say that I'm not quite clear about the conceptual differences
among the two. I know that SQL Profiles put in a lot of OPT_ESTIMATE
hints and adjust cardinality estimates. I know that baselines are
actually plans but their usage seems extremely similar. Can anyone shed
some light on the conceptual differences?



--
http://mgogala.byethost5.com

On Oct 10, 9:17*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
I'm not there yet, but http://jonathanlewis.wordpress.com/2...lan-baselines/
is a pretty good start, especially the Kerry Osborne note on
licensing.

jg
--
@home.com is bogus.
More like Zzzzz than buzzword bingo:
http://news.techeye.net/software/ell...etwork-baffles

On 10.10.2011 18:17, Mladen Gogala wrote:
I think a baseline contains the data on which profile creation is based:

http://download.oracle.com/docs/cd/E...t.htm#autoId15

The more abstract definition
http://download.oracle.com/docs/cd/E...ew.htm#autoId4

As I understand it the baseline contains the base data which is used to
identify critical SQL which then is further analyzed during maintenance
windows. IIRC you can even have Oracle automatically accept new plans
(probably stored in profiles) if the execution characteristics improve
vs. the current plan.

http://download.oracle.com/docs/cd/E...e.htm#CHDHICFI

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Mon, 10 Oct 2011 19:09:08 +0200, Robert Klemme wrote:

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.



--
http://mgogala.byethost5.com

Mladen Gogala <gogala.mladen (AT) gmail (DOT) com> wrote in
newsan.2011.10.10.18.35.46 (AT) gmail (DOT) com:

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/en...nt_part_1_of_4
_creating_sql_plan_baselines

http://blogs.oracle.com/optimizer/en...nt_part_2_of_4
_spm_aware_optimizer

http://blogs.oracle.com/optimizer/en...nt_part_3_of_4
_evolving_sql_plan_baselines_1

http://blogs.oracle.com/optimizer/en...nt_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/en...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/ta...lan_management
=====

HTH

Cheers!

--
Jeroen

TheBoss <TheBoss (AT) invalid (DOT) nl> wrote in
news:Xns9F7AF386D56EATheBossUsenet (AT) 194 (DOT) 109.133.133:

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/2...acle-using-my-
outline-profile-baseline/

http://kerryosborne.oracle-guy.com/2...-sql-profiles/

--
Jeroen

On Mon, 10 Oct 2011 22:01:37 +0000, TheBoss wrote:


Thanks Jeroen. It seems that I have the rest of this evening planned for
me.



--
http://mgogala.byethost5.com

On Oct 10, 6:17*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
It looks like that the underlying basic principle of both, SQL
Profiles and Baselines are still the same as Stored Outlines: A set of
hints that are applied to a SQL Statement.

However, there are many differences:

1. SQL Profiles are supposed to help the optimizer to come up with a
better execution plan that is based on better cardinality and cost
estimates. Hence a SQL Profile is usually generated by the SQL Tuning
Advisor that runs the CBO in "offline" optimization mode where parts
of the SQL statement are actually executed to compare the estimated
with the actual cardinalities. Based on that offline optimization the
SQL Profile will contain corresponding "correction" hints that allow
to correct statistics and cardinalities.

This also means that SQL Profiles are only available with EE +
Diagnostic + Tuning Pack. SQL Plan Baselines is an EE feature that
doesn't require any additional options (not sure why it is not
available with SE though).

2. SQL Plan Baselines are complete sets of hints (the same as Stored
Outlines in that respect) that "lock in" a plan. However, in
comparison to Stored Outlines they offer the following advantages:

* The SQL Plan Baseline also stores the PLAN_HASH_VALUE of the desired
execution plan. If after applying the stored hints the plan cannot be
reproduced (meaning it results in a different PLAN_HASH_VALUE) the
baseline will be rejected / ignored and the CBO will continue with
normal costing. This should prevent issues that can happen with Stored
Outlines that cannot be applied completely but will potentially result
in a "disaster" plan due to partially applied hints

* There can be multiple accepted plans for the same SQL Statement (you
can have only a single Stored Outline) - this is for example crucial
for Adaptive Cursor Sharing to work in conjunction with SQL Plan
Baselines or other "multiple child cursors with different plans"
scenarios.

* The CBO does normal costing in addition to the accepted SQL Plan
Baseline and adds "non-accepted" plans that can be verified and
evolved if they turn out to be superior to the locked plan(s). This
allows Plan Stability but at the same time gives the ability to arrive
at better plans if desired (requires some kind of activity, an
automatic evolution is not enabled by default). This addition of un-
accepted plans can be avoided by marking the SQL Plan Baseline as
"fixed" - this should potentially speed up the parsing phase but of
course defeats one of the main advantages of SQL Plan Baselines over
Stored Outlines.

* SQL Plan Baselines allow to apply a set of hints to an unhinted
version of the same statement easily - as shown in various blogs like
Jonathan's or the Optimizer Group. So this is basically the ability to
get the desired plan for a given SQL Statement by applying hints but
without the need to change the underlying application code by adding
hardcoded hints. The same could be achieved with Stored Outlines but
was a quite messy process

So you can think of SQL Plan Baselines as "Stored Outlines on
steroids". And as described in the mentioned blog posts you can have
both, a SQL Profile and SQL Plan Baseline for the same SQL Statement.
The SQL Profile will be used by the CBO to generate the execution
plan. If there are for example multiple accepted plans for the same
SQL Statement, then the SQL Profile might drive the CBO to use another
accepted plan than without the SQL Profile.

And finally - yes, you can "misuse" SQL Profiles easily to act like a
Stored Outline by manually generating a SQL Profile that contains a
(full) set of hints. The SQL Profile doesn't offer the above mentioned
features though. There is one particular feature however that is
unique to SQL Profiles: You can enable the "force matching" option
that basically allows to apply the SQL Profile to any SQL Statement
that would be similar if literals were replaced by bind variables
(like CURSOR_SHARING = FORCE) - this is something that is not possible
with SQL Plan Baselines, they only work with exact text matching and
are therefore useless for SQL Statements that differ only in literals.

I've elaborated about this here:
http://oracle-randolf.blogspot.com/2...-existing.html

Hope this helps,
Randolf

On 10 Okt., 18:17, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
Another good read that should not be left unmentioned: http://intermediatesql.com

Search for the expression "SQL Plan Management", and a few interesting
posts will pop up.

Hope this helps,
Randolf