Re: Strange cost difference

On Feb 19, 3:30 am, Mladen Gogala <mgog..._at_no.address.invalid> wrote:
> On Fri, 18 Feb 2011 09:30:16 -0800, joel garry wrote:> > Maybe I'm not understanding something, but isn't the idea for Oracle to> > figure out the best plan over time for each statement?>> Actually no. Oracle 11 will collect statistics and replace the baseline> in the SYSMAN tablespaces if and only if the plan costs less than the> existing one. Oracle is not Watson, doesn't play Jeopardy with our SQL,> it simply saves the plans and replaces them with cheaper plans.

I think the descriptions here for SQL baselines are a bid misleading.
Oracle never replaces any active SQL baselines, it only adds new
execution plans but doesn't activate them automatically. In order to
activate ("accept") a SQL baseline, it needs to be "evolved", and this
means, if not forced by the user, that the statement will actually be
executed (twice) and the new plan's runtime profile of the execution
will be compared to the runtime profile of the currently active SQL
baseline. Only if the runtime profile is superior, the SQL baseline
will be "accepted".

So no one has to fear that a plan that has a lower cost will
automatically replace an existing plan of a SQL baseline.

The primary usage of SQL baselines is Plan Stability (e.g. migration
to new release), and they conveniently offer the feature to apply a
baseline of one statement to the baseline of another statement, which
was not that simple with Stored Outlines.

The only thing that they don't offer (yet) as far as I know is the
special feature of SQL Profiles that they can replace literals with
binds for text matching, which means that a single SQL Profile can be
applied to different SQLs if they differ only in literals.

However, since SQL Profiles require Enterprise Edition + Diagnostics +
Tuning Pack, most things can be addressed with SQL baselines without
any further license hurdles.