Re: EXPLAIN PLAN : better with RULE!

First, I'm sorry for my poor english... I'm french ;)

Then, yes, all tables have been analyzed, but with estimate. I will try again with compute all!

Finaly, I think that your last sentence must be "If just one table has stats,
> then CHOOSE will use the RBO."? :)

Thanks for all!

"Richard Foote" <richard.foote_at_bigpond.com> a écrit dans le message news: ygUY8.36208$Hj3.109191_at_newsfeeds.bigpond.com...
> Hi
>
> But your previous statement "but is CBO not supposed, when no stats,
knowing
> at least that indexes exist on tables?" (poorly written as it is) doesn't
> support your statement that you know all that !! No stats, Oracle makes
the
> assumption tables are small, FTS most efficient way to access such tables.
>
> Question and we need a clear answer on this. Have you analyzed ALL the
> tables in this query of yours ? If you haven't then based on my little
> lesson (glad you liked it) FTS are to be expected and poor execution plans
> and poor performance is to be expected as well. If just one table has
stats,
> then CHOOSE will use the CBO.
>
> Trying to help :)
>
> Richard
>
> "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> news:ah120h$eob$1_at_wanadoo.fr...
> > Hi Richard!
> >
> > I know all of that!!
> > However, the query which explain plan is in my first message
> > run very faster with RBO than with CBO, and THAT is my "problem"!
> > ... As I know that FTS is better than use of an index with poor
> > selectivity... ;-)
> >
> > I thank you all the same for this lesson, I am sure that it can take
> > advantage to somebody!
> >
> > Cordially.
> >
> > "Richard Foote" <richard.foote_at_bigpond.com> a écrit dans le message
news:
> > 9bTY8.36123$Hj3.107818_at_newsfeeds.bigpond.com...
> > > Hi Epicentre Team B Annecy,
> > >
> > > You are making a common but often erroneous assumption that the use of
> an
> > > index is *always* better than a FTS. Fortunately the CBO does not make
> > this
> > > assumption, hence why indexes may not be used.
> > >
> > > Now I have always had a soft spot for the little battler and I an
> official
> > > member of the "Hey, Stop Picking On The Poor FTS, It's A Better Access
> > Path
> > > Than Many Give It Credit For Society", or HSPOTPFTSIABAPTMGICFS or
short
> > :)
> > >
> > > A few key points.
> > >
> > > If by accessing an index to read x number of rows is *more expensive*
> than
> > > to use a FTS, then a FTS my friend is the way to go. The FTS has three
> > > really really big advantages over an index.
> > >
> > > 1) A conventual read of an index can *only* access data one block at a
> > time.
> > > Each read of an index block, one logical I/O, each subsequent access
to
> > the
> > > table, one logical I/O. A FTS can read multiple blocks in one I/O.
This
> is
> > > set (and tuned) via the db_file_multiblock_read_count parameter but
lets
> > say
> > > it's set to 8. This means it can effectively read the table in 'blocks
> > under
> > > the HWM/8' logical I/Os. Very important point.
> > >
> > > 2) Each block in a table is only ever accessed *once* during a FTS.
When
> > > accessing via an index, a bock may be visited many times (up to the
> number
> > > of rows that reside in the block). Each time it needs to be reaccessed
> is
> > an
> > > additional logical I/O. In theory, if you were to read every row via
an
> > > index, the number of logical I/Os would be 'the majority of blocks in
> the
> > > index + the number of rows in the table'. Very important point. A FTS
is
> > > remember 'number of *blocks*/8'
> > >
> > > 3) A FTS can be performed in parallel while a conventual index scan
can
> > not.
> > > Assuming we have an effective degree of parallelism of 8, this means
we
> > can
> > > read an entire table in approximately the same time it takes an index
to
> > > read 'number of blocks/8/8' I/Os. Very very important point.
> > >
> > > The question the CBO asks is what is the break even point. At what
point
> > is
> > > it more efficient to use an index, at what point is it more efficient
to
> > > read the whole damn lot. This of course depends. Not so much on the
> > > 'percentage of rows' that need to be accessed, but more so on the
> > proportion
> > > of 'blocks' that one method uses over the other based on the above
three
> > > points.
> > >
> > > If accessing one row, then the cost of the index is say root
> block+branch
> > > block+ leaf block in the index + one data block. Four logical I/Os
> > (although
> > > the CBO might be clever enough to assume some of the index blocks may
> > > already be in memory). To read the table via FTS is say 1000 table
> blocks
> > /8
> > > or 125 logical I/Os hence the index wins.
> > >
> > > To read say 10/% of the table requires say, one leaf block+one branch
> > block+
> > > say 100 leaf blocks + say 1000 table blocks (ie. approx 10 rows per
> block)
> > > which equals 1102 logical I/Os. To read the table via FTS is say 1000
> > tables
> > > blocks / 8 or 125 logical I/Os (again). Hence, FTS wins and wins
easily.
> > >
> > > If tables are not analyzed (and therefore Oracle has no statistics)
how
> > can
> > > Oracle compute the above calculations. It can't. In fact what Oracle
> does,
> > > is assume the tables are really small, tiny, not deserving of
> statistics.
> > So
> > > small in fact that they can be read with one logical I/O (ie. are less
> > than
> > > the db_file_multiblock_read_count of blocks). *Note one logical I/O to
> > read
> > > this table* (which is far better than 2 logical I/O that the best
index
> > can
> > > offer !!). That's why the CBO ignores indexes on non analyzed tables.
> > >
> > > So in summary, although you say the CBO is not using the indexes and
> that
> > it
> > > should be aware of them, note it *is* aware of them but it determines
> that
> > > it's less expensive to read the whole table anyway.
> > >
> > > I could go on but I hope this all makes sense and helps in your
> > > understanding of what might be going on.
> > >
> > > Good Luck
> > >
> > > Richard
> > >
> > >
> > > "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> > > news:ah0qqj$9oq$1_at_wanadoo.fr...
> > > > I know the influence of statistics for CBO,
> > > > and I know that when no stats, CBO makes join from left to right
> > > > in the FROM clause,
> > > > but is CBO not supposed, when no stats, knowing at least that
indexes
> > > exist
> > > > on tables?
> > > >
> > > > Thanks again.
> > > >
> > > > "Loadrunner" <load_at_runner.com> a écrit dans le message news:
> > > > 1026810930.9839.0.nnrp-10.c2d92da9_at_news.demon.co.uk...
> > > > > Can you confirm that you have analyzed tables with the "For Table
> For
> > > All
> > > > > Columns For All Indexes" clause?
> > > > >
> > > > > If very large table estimate the statistics.
> > > > >
> > > > > The explain plan produced when CBO active can vary dramatically
with
> > or
> > > > > without statistics.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Tue Jul 16 2002 - 08:15:37 CDT