Try FIRST_ROWS as a hint. See what you get.
Also post the full output from PLAN_TABLE for the statement_id for that
explain and we will explain to you what is going on.
in 8.1.7 Analyze estimate with no sample only does 1064 rows . And you
shouldn't be using it.
in 9.2 you should be using DBMS_STATS
Are you using DBMS_STATS ? ANALYZE is being deprecated, although it should
still be valid for chaining and validation
and post the !!!!!!!!!!!!!!!!! VERSION !!!!!!!!!!!!!
"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:55:50 CDT
