Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!
In RBO the rule was: Index good, FTS bad. You had no other choice,
even when the IO cost of scanning the index and table exceeded the
cost of a single fts many times. Don't fall into the trap of thinking
indexes are always the best access method. If a plan uses two FTS in a
hash join verses a nested loop driven by one of the tables forcing
multiple scans of the other, or one of its indexes (even a PK) the
hash may be less cost in IO. Since IO translates to disk access and
disk access is the number one cause of slow queries, any plan that
reduces disk IO will usually perform better.
Many things effect the cost of an index, the number of levels, the physical size and the clustering factor for example.
Mike
afilonov_at_yahoo.com (Alex Filonov) 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 Wed Jul 17 2002 - 08:04:57 CDT