Very Computer

by **Epicentre Team B Annec** » Wed, 17 Jul 2002 17:05:31

Hi!

I've a query which join a quite big table with a (complex) view,
and the explain plan with CBO (default) gives me plenty of Table Access
Full,
whereas RBO (forced with /*+RULE*/) uses all primary indexes!!
And the problem is that CBO is much more expensive in this case!?

Do someone have an idea?

Here are the 2 explain plans:

SELECT STATEMENT Optimizer=HINT: CHOOSE

SORT (GROUP BY)
NESTED LOOPS
TABLE ACCESS (FULL) OF 'CATALOG'

VIEW OF 'PRODUCTS'
UNION-ALL
INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

TABLE ACCESS (FULL) OF 'PROD_CAT2'

INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

FILTER
TABLE ACCESS (FULL) OF 'TRANS2'

FILTER
TABLE ACCESS (FULL) OF 'TRANS_F'

TABLE ACCESS (FULL) OF 'PROD_CAT3'

TABLE ACCESS (FULL) OF 'PROD_LIV'

With RULE :

SELECT STATEMENT Optimizer=HINT: RULE
SORT (GROUP BY)
NESTED LOOPS
VIEW OF 'PRODUCTS'
UNION-ALL
CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

CONCATENATION
FILTER
TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

FILTER
TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

FILTER
TABLE ACCESS (FULL) OF 'TRANS_F'
CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'CATALOG'
INDEX (UNIQUE SCAN) OF 'CATALOG_PK_PRIM' (UNIQUE)

Thanks!

Colin.

by **Loadrunne** » Wed, 17 Jul 2002 18:17:46

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.

by **Telemachu** » Wed, 17 Jul 2002 18:47:32

WHAT VERSION OF THE PRODUCT ?

What happens if you use FIRST_ROWS ?

Are the tables analyzed ? What percentage ? is it a full compute on all cols
?

You should be aware that RBO is being desupported.

And you have not said either if CBO is slower than RBO - only that it's more
expensive.

However, if you just want sticking plaster you can store the plan outline so
that oracle will always use that plan for that SQL statement.

Quote:> Hi!

> I've a query which join a quite big table with a (complex) view,
> and the explain plan with CBO (default) gives me plenty of Table Access
> Full,
> whereas RBO (forced with /*+RULE*/) uses all primary indexes!!
> And the problem is that CBO is much more expensive in this case!?

> Do someone have an idea?

> Here are the 2 explain plans:

> SELECT STATEMENT Optimizer=HINT: CHOOSE

> SORT (GROUP BY)
> NESTED LOOPS
> TABLE ACCESS (FULL) OF 'CATALOG'

> VIEW OF 'PRODUCTS'
> UNION-ALL
> INLIST ITERATOR
> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> TABLE ACCESS (FULL) OF 'PROD_CAT2'

> INLIST ITERATOR
> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> FILTER
> TABLE ACCESS (FULL) OF 'TRANS2'

> FILTER
> TABLE ACCESS (FULL) OF 'TRANS_F'

> TABLE ACCESS (FULL) OF 'PROD_CAT3'

> TABLE ACCESS (FULL) OF 'PROD_LIV'

> With RULE :

> SELECT STATEMENT Optimizer=HINT: RULE
> SORT (GROUP BY)
> NESTED LOOPS
> VIEW OF 'PRODUCTS'
> UNION-ALL
> CONCATENATION
> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> CONCATENATION
> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> CONCATENATION
> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> CONCATENATION
> FILTER
> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> FILTER
> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> FILTER
> TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> FILTER
> TABLE ACCESS (FULL) OF 'TRANS_F'
> CONCATENATION
> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> CONCATENATION
> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> TABLE ACCESS (BY INDEX ROWID) OF 'CATALOG'
> INDEX (UNIQUE SCAN) OF 'CATALOG_PK_PRIM' (UNIQUE)

> Thanks!

> Colin.

by **Epicentre Team B Annec** » Wed, 17 Jul 2002 19:00:34

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.

Quote:> 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.

by **Tunin** » Wed, 17 Jul 2002 19:05:31

When will RBO being desupported?????
I saw articles with use of RBO in Oracle 9i (I use 8.1.7)!!

> WHAT VERSION OF THE PRODUCT ?

> What happens if you use FIRST_ROWS ?

> Are the tables analyzed ? What percentage ? is it a full compute on all
cols
> ?

> You should be aware that RBO is being desupported.

> And you have not said either if CBO is slower than RBO - only that it's
more
> expensive.

> However, if you just want sticking plaster you can store the plan outline
so
> that oracle will always use that plan for that SQL statement.

> > Hi!

> > I've a query which join a quite big table with a (complex) view,
> > and the explain plan with CBO (default) gives me plenty of Table Access
> > Full,
> > whereas RBO (forced with /*+RULE*/) uses all primary indexes!!
> > And the problem is that CBO is much more expensive in this case!?

> > Do someone have an idea?

> > Here are the 2 explain plans:

> > SELECT STATEMENT Optimizer=HINT: CHOOSE

> > SORT (GROUP BY)
> > NESTED LOOPS
> > TABLE ACCESS (FULL) OF 'CATALOG'

> > VIEW OF 'PRODUCTS'
> > UNION-ALL
> > INLIST ITERATOR
> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (FULL) OF 'PROD_CAT2'

> > INLIST ITERATOR
> > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> > FILTER
> > TABLE ACCESS (FULL) OF 'TRANS2'

> > FILTER
> > TABLE ACCESS (FULL) OF 'TRANS_F'

> > TABLE ACCESS (FULL) OF 'PROD_CAT3'

> > TABLE ACCESS (FULL) OF 'PROD_LIV'

> > With RULE :

> > SELECT STATEMENT Optimizer=HINT: RULE
> > SORT (GROUP BY)
> > NESTED LOOPS
> > VIEW OF 'PRODUCTS'
> > UNION-ALL
> > CONCATENATION
> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> > CONCATENATION
> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> > CONCATENATION
> > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> > CONCATENATION
> > FILTER
> > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> > FILTER
> > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> > FILTER
> > TABLE ACCESS (FULL) OF 'TRANS_F'
> > CONCATENATION
> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> > CONCATENATION
> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> > TABLE ACCESS (BY INDEX ROWID) OF 'CATALOG'
> > INDEX (UNIQUE SCAN) OF 'CATALOG_PK_PRIM' (UNIQUE)

> > Thanks!

> > Colin.

by **Richard Foot** » Wed, 17 Jul 2002 19:32:16

Tuning my friend, the final sod of earth will befall RBO come 10i.

RIP

Richard

> When will RBO being desupported?????
> I saw articles with use of RBO in Oracle 9i (I use 8.1.7)!!

> > WHAT VERSION OF THE PRODUCT ?

> > What happens if you use FIRST_ROWS ?

> > Are the tables analyzed ? What percentage ? is it a full compute on all
> cols
> > ?

> > You should be aware that RBO is being desupported.

> > And you have not said either if CBO is slower than RBO - only that it's
> more
> > expensive.

> > However, if you just want sticking plaster you can store the plan
outline
> so
> > that oracle will always use that plan for that SQL statement.

> > > Hi!

> > > I've a query which join a quite big table with a (complex) view,
> > > and the explain plan with CBO (default) gives me plenty of Table
Access
> > > Full,
> > > whereas RBO (forced with /*+RULE*/) uses all primary indexes!!
> > > And the problem is that CBO is much more expensive in this case!?

> > > Do someone have an idea?

> > > Here are the 2 explain plans:

> > > SELECT STATEMENT Optimizer=HINT: CHOOSE

> > > SORT (GROUP BY)
> > > NESTED LOOPS
> > > TABLE ACCESS (FULL) OF 'CATALOG'

> > > VIEW OF 'PRODUCTS'
> > > UNION-ALL
> > > INLIST ITERATOR
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (FULL) OF 'PROD_CAT2'

> > > INLIST ITERATOR
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> > > FILTER
> > > TABLE ACCESS (FULL) OF 'TRANS2'

> > > FILTER
> > > TABLE ACCESS (FULL) OF 'TRANS_F'

> > > TABLE ACCESS (FULL) OF 'PROD_CAT3'

> > > TABLE ACCESS (FULL) OF 'PROD_LIV'

> > > With RULE :

> > > SELECT STATEMENT Optimizer=HINT: RULE
> > > SORT (GROUP BY)
> > > NESTED LOOPS
> > > VIEW OF 'PRODUCTS'
> > > UNION-ALL
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)

> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)

> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)

> > > CONCATENATION
> > > FILTER
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> > > FILTER
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)

> > > FILTER
> > > TABLE ACCESS (FULL) OF 'TRANS_F'
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)

> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)

> > > TABLE ACCESS (BY INDEX ROWID) OF 'CATALOG'
> > > INDEX (UNIQUE SCAN) OF 'CATALOG_PK_PRIM' (UNIQUE)

> > > Thanks!

> > > Colin.

by **Telemachu** » Wed, 17 Jul 2002 20:04:37

"When no stats" it uses RBO.

Without stats the CBO cannot work effectively.

and STOP supposing !

the index could be bigger than the table.

Try with 100% stats on everything( do not forget system stats or indexes ) .
Then we will look at the plan generated by CBO

ALL and FIRST row hints should then be the major modifiers.

> 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.

> > 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.

by **Richard Foot** » Wed, 17 Jul 2002 20:17:38

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

> 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.

> > 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.

by **Epicentre Team B Annec** » Wed, 17 Jul 2002 21:03:12

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.

> 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

> > 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.

> > > 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.

by **Richard Foot** » Wed, 17 Jul 2002 21:32:20

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

> 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.

> > 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

> > > 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.

> > > > 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.

by **Vladimir M. Zakharyche** » Wed, 17 Jul 2002 21:31:45

Oracle mentioned desupporting RBO 'some time and release in the future'
starting with 7.3 or 8.0. Now they finally made up their minds and released
official alert, which states that RBO will no longer be available in next
major Oracle release (speculatively named 10i so far). Among several
reasons for dumping RBO the most notable were #1: 'RBO was not enhanced
since 7.3' and #2: 'RBO does not support many of the new database features
introduced since version 7, because [see #1], and gets in the way of CBO
when these and other features are used.' So they advise all their customers
to certify and tune their applications for CBO-only environment and do not
rely on RBO anymore.

--

Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

> When will RBO being desupported?????
> I saw articles with use of RBO in Oracle 9i (I use 8.1.7)!!

> > WHAT VERSION OF THE PRODUCT ?

> > What happens if you use FIRST_ROWS ?

> > Are the tables analyzed ? What percentage ? is it a full compute on all
> cols
> > ?

> > You should be aware that RBO is being desupported.

> > And you have not said either if CBO is slower than RBO - only that it's
> more
> > expensive.

> > However, if you just want sticking plaster you can store the plan outline
> so
> > that oracle will always use that plan for that SQL statement.

> > > Hi!

> > > I've a query which join a quite big table with a (complex) view,
> > > and the explain plan with CBO (default) gives me plenty of Table Access
> > > Full,
> > > whereas RBO (forced with /*+RULE*/) uses all primary indexes!!
> > > And the problem is that CBO is much more expensive in this case!?