Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!

Re: EXPLAIN PLAN : better with RULE!

From: Mike Ault <mikerault_at_earthlink.net>
Date: 17 Jul 2002 06:04:57 -0700
Message-ID: <37fab3ab.0207170504.61c2be00@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US