Re: Strange cost difference
Date: Sat, 19 Feb 2011 10:52:35 -0800 (PST)
Message-ID: <0ade6d8a-4e05-4600-a833-50274b5b411d_at_k17g2000pre.googlegroups.com>
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.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sat Feb 19 2011 - 12:52:35 CST