No Richard is right. NO stats implies the RBO, stats on any one of the
tables causes the CBO to kick in (which then assumes all the other tables
are one IO in size). Thus stats on one table is likely worse than no stats.
Also the CBO will be used if you use certain database features which were
introduced after the RBO was frozen.
Net result it is dead easy to end up using the CBO with few or no stats and
then curse it.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Tuning" <c_armanet_at_hotmail.com> wrote in message
news:ah168a$4cq$1_at_wanadoo.fr...
> 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:48:01 CDT
