I was comparing 2 SQL statements which produce the same result. Here are
the statements:

with e(empno,mgr,lev) as (
select empno,mgr,1 from emp where empno=7839
union all
select emp.empno,emp.mgr,e.lev+1
from emp,e
where emp.mgr=e.empno)
select * from e

select empno,mgr,level
from emp
connect by prior empno=mgr
start with empno=7839

I executed both statements with autotrace on and here is the problem:
Execution Plan
----------------------------------------------------------
Plan hash value: 2925328376

--------------------------------------------------------------------------------
--------------------

t (%CPU)| Time |

--------------------------------------------------------------------------------
--------------------

6 (17)| 00:00:56 |

6 (17)| 00:00:56 |

1 (0)| 00:00:11 |


5 (20)| 00:00:46 |

3 (0)| 00:00:31 |

--------------------------------------------------------------------------------
--------------------


Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("EMPNO"=7839)
5 - access("EMP"."MGR"="E"."EMPNO")

Note
-----
- SQL plan baseline "SQL_PLAN_c3fy5a6wn21t0f51d791e" used for this
statement

The question is why does oracle think that this statement will return 15
rows? Statistics is there, the table is fully analyzed. With the "connect
by" formulation, there is no such problem:

Execution Plan
----------------------------------------------------------
Plan hash value: 763482334

--------------------------------------------------------------------------------
----------------

CPU)| Time |

--------------------------------------------------------------------------------
----------------



--------------------------------------------------------------------------------
----------------


Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("MGR"=PRIOR "EMPNO")
filter("EMPNO"=7839)

Note
-----
- SQL plan baseline "SQL_PLAN_7myzsfbbvmbs0acb9d868" used for this
statement

Consequently, Oracle thinks that the 1st statement is more expensive. The
version is 11.2.0.2. Recursive CTE are supported as of Oracle 11.2, so
there wasn't much of a need to specify this. It won't work on 10G or
lower.
Also, execution paths are showing internal optimization in both cases. I
like the recursive CTE because I can start level with 5 or 3.14, if
that's what's needed.

--
http://mgogala.byethost5.com

On Thu, 17 Feb 2011 22:34:02 +0000, Mladen Gogala wrote:

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

On Feb 17, 6:02*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
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?

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-.../dp/1430226684

On Feb 18, 4:14*am, Randolf Geist <mah... (AT) web (DOT) de> wrote:
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/...30034699868 1

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
--
@home.com is bogus.
http://knowyourmeme.com/memes/i-for-...ew-x-overlords

On Fri, 18 Feb 2011 09:30:16 -0800, joel garry wrote:

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.
Although, speaking of Watson and intelligence, I predict that we will
soon see multi-TB NVRAM devices, and the face of the computing will
definitely change. Among other things, B*Trees may become obsolete.



--
http://mgogala.byethost5.com

Mladen Gogala wrote,on my timestamp of 19/02/2011 1:30 PM:
Looks good, but I think the fear there was that the plan will change to a lesser
cost one which will result in a longer execution. Happened before, will happen
again, plenty of examples of such.


Well, one of the enhancements we did when we started to use partitioning in our
DW was to lose quite a few unnecessary indexes. In one case we dropped 6
indexes! Turned out we got nearly an order of magnitude faster in *all*
statements involving those tables by just partition pruning and keeping the size
of each partition quite small and the PK as a local index.

I've long been a great fan of catalogs as opposed to searching with/without
indexes. Way back when, large libraries never indexed books: they just put each
book in a catalog category. All you had to do was linearly search one shelf of
that category to find what you wanted. After consulting the catalog.
The interesting thing of course is that the catalog itself used indexing for the
searches.

I think that's what will happen: you'll see two-tier location strategies in
future. The first using indexes, the next using a catalog "shelf", pointed to by
the index.

And what's best: (re!)building the index will not be a legacy task that only
your grand-children will see the end of.
(gd&r, vvf)

On Feb 19, 3:30*am, Mladen Gogala <mgog... (AT) no (DOT) address.invalid> wrote:
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-.../dp/1430226684