Re: Optimizer and block size changes = big problem.

On Wed, 09 May 2001 20:03:22 GMT, Ricky Sanchez <rsanchez_at_more.net> wrote:

>Okay Nuno, you seem from your posts like a glutton for punishment, so

that's me allright... :-)

>here is my suggestion: Set 10053 on the mysterious queries and wade
>through the trace output and figure out what CBO is thinking. CBO will
>always choose the least cost plan, but it is not always clear how it
>arrives at its cost value. The 10053 event, at any level, will trace the
>optimizers plans. Bitch to read and wade through, but the info is all
>there.

Will do. Thanks for the tip.

>
>Bear in mind that for really huge multi-way joins, the plan permutations
>could exceed the default 8,000 maximum, so some plans may not be
>considered. Not normally likely, but possible.

80000. I thought of dropping that. But the point is it was that way before, why did it misbehave by just changing the block size?

>
>Do be cautious with db_file_multiblock_read_count, since it servers two
>purposes. 1) Attempt to get that size of I/O in multiblock reads and 2)
>use that value in costing full table scans. Overstating it, as you
>obviously know, will make fts appear cheaper. Might help some queries
>while killing others.
>

But-but-but.... My DFMR was 8 on 16K blocks, then I made it 16 for 8K, got burned, dropped it down to 8 again and it *still* was doing HASH join all over the bloody place! No change whatsoever!

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Thu May 10 2001 - 04:16:49 CDT