Re: EXPLAIN PLAN : better with RULE!

Hi Alex,

Can you say more about the number of permutations needed for an efficient CBO
plan growing proportional to the factorial of the tables involved? Given a query, can
I calculate the number of permutations needed to come up with an effective plan?

For example, have some queries that involve 25 or more tables, and have my optimizer_max_permutations set to 80000. But the CBO typically comes up with poor plans. I've had best luck using RBO and carefully ordering the tables in the
FROM clause. I am using a large DB_FILE_MULTIBLOCK_READ_COUNT (32), which I think causes the CBO to often assume that FTS is better than indexes.

--steve

"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.
Received on Wed Jul 17 2002 - 13:34:42 CDT