Re: EXPLAIN PLAN : better with RULE!

"Steve Mitchell" <stevem_at_hdcsi.com> wrote in message news:<6NiZ8.967$XT7.69047327_at_newssvr21.news.prodigy.com>...
> 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?
>

You can estimate. In reality, it depends also on number of indexes, table segments and lots of other things. However, you can safely bet that if you have more than 7 tables in your query, CBO is not gonna give you optimal plan, ever.

> 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

As far as I remember, 80000 is max for 8i. Haven't tried to find max for 9i yet. Oracle actually can't increase max value of it because parsing will use a lot of resources and take too long time.

> 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.

Not exactly. When you have more than 10 tables, CBO just can't find optimal plan. It's fairly good when you have 5 or less tables in a query, doesn't work at all if you have more than 10 and spotty in between. It's the nature of the beast. Finding optimal plan is poorly defined task of non-linear natural number optimization. There is no common solution or method for such tasks, most are solved by brute force method, which has number of calculations proportional to factorial of the task dimension.

Again, if you have a complex query, prepare to tune it manually. CBO is not going to help you here. And, sometimes FTS is really better than using 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 Thu Jul 18 2002 - 10:44:39 CDT