Re: EXPLAIN PLAN : better with RULE!

Hi Alex,

May I ask a quick question.

I'm curious to know what do you mean when you say the RBO is much worse in 8i ?

Cheers

Richard
"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0207161208.13ae77b3_at_posting.google.com...
> "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
news:<ah0k2t$4j0$1_at_wanadoo.fr>...
>
> What's value of parameter optimizer_max_permutations? Try to increase it
to
> something above 50000.
>
> My humble experience tells me that any complex query needs manual tuning.
> Even rule based optimizer is not very good here. With CBO you are
severerly
> restricted by above mentioned parameter. You can't make it really big, and
real
> number of permutations needed for efficient CBO plan is growing
proportional to
> factorial of tables involved. So you know were to shove all advices about
> analyzing, parameter setting and all, right?
> There are 2 real problems here.
> 1. Rule based optimizer on 8.1 is much worse than it was in previous
versions.
> 2. Manual tuning is time consuming and requires real understanding of
tuning.
> But this is the only way to go.
> I'm happy for you actually that RULE hint works OK. Just leave it this way
and
> don't worry. And if it's not good enough, than you need to use combination
of
> hints to force the plan you like.
>
> HTH
>
> > 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.
Received on Tue Jul 16 2002 - 22:51:51 CDT