Re: EXPLAIN PLAN : better with RULE!

"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 - 15:08:54 CDT