Re: EXPLAIN PLAN : better with RULE!

On Wed, 17 Jul 2002 18:34:42 GMT, "Steve Mitchell" <stevem_at_hdcsi.com> wrote:

>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

You'd better playing around with first_rows and the init.ora parameter optimizer_indx_cost_adj, instead of RBO. Also for CBO the tables need to be ordered *the other way around*, from left to right.
Increasing DB_FILE_MULTI_BLOCK_READ_COUNT is a bad idea, and it is even worse when this parameter multiplied by db_block_size exceeds 64k.
Most O/Ses won't read more than 64k in one go.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jul 17 2002 - 16:15:52 CDT