Re: Strange cost difference
Date: Fri, 18 Feb 2011 09:30:16 -0800 (PST)
Message-ID: <a20f551a-3f9d-4171-a34e-022c275c0853_at_o7g2000prn.googlegroups.com>
On Feb 18, 4:14 am, Randolf Geist <mah..._at_web.de> wrote:
> On Feb 17, 6:02 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
>
> > On Thu, 17 Feb 2011 22:34:02 +0000, Mladen Gogala wrote:
> > > The question is why does oracle think that this statement will return 15
> > > rows?
>
> > I figured it out. The recursive CTE has 2 parts: the initial and the
> > recursive part. The part of the 1st statement which says
>
> > "select empno,mgr,1 from emp where empno=7839"
>
> > plays the same role as the "start with empnp=7839" part of the "connect
> > by" version, but is apparently executed separately. It seems to me that
> > the good, old "connect by" stuff is still preferred by Oracle.
>
> > --http://mgogala.byethost5.com
>
> You can get a very similar plan for the CONNECT BY variant if you use
> the "CONNECT_BY_FILTERING" hint - your simple query is using the
> "NO_CONNECT_BY_FILTERING" variant which basically corresponds to the
> old connect by implementation if I remember correctly.
>
> The costs will be quite similar I believe since the plan will be
> almost identical (in this particular case).
>
> Of course it might not work out as expected since you seem to have
> baselines activated for both statements - I wonder why?
I've been trying to understand baselines at a distance (as in Kerry
Osborne and Jonathan's blogs), and haven't quite yet. But I do see
"...but yes, you would likely want baselines in place, knowing that in
11gr2 there are even more options with them, but they will give you
"consistent plans" over time. " on
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2060190300346998681
Maybe I'm not understanding something, but isn't the idea for Oracle to figure out the best plan over time for each statement? I would guess things might diverge significantly with the separate initial execution Mladen described, given much larger and more skewed data sets. Perhaps using baselines to make plans work as they worked in a previous version is not the primary purpose of baselines?
jg
-- _at_home.com is bogus. http://knowyourmeme.com/memes/i-for-one-welcome-our-new-x-overlordsReceived on Fri Feb 18 2011 - 11:30:16 CST