Re: Cost Base Optimizer on 3 table select

richard.endo_at_unisys.com wrote in message <70lb8u$gke$1_at_nnrp1.dejanews.com>...

<snipped>
>This was the most efficient plan in terms of the number of total buffer
gets.
>I've recreated the db on another multiprocessor machine and I can't get the
>same execution plan.

<snipped>
>with the 'BUILDING' value. Some of the plans like to do full table scans
>using parallel query even though I specify 0 on the optimize_parallel?
>parameter.

It sounds that you may have PQ enabled on one database and not the other.

>I would like to get the efficient execution plan without explicit hints and
>not changing the select statement.

Sadly, that is not a good idea when using CBO. Experience has taught me that the safest way to write SQLs that perform consistently on Oracle using the same execution plan is via hints.

A simple example. Had this month-end SQL that performed differently each time. Sometimes fast - sometimes incredibly slow. The reason - because the table was so large we could only do a 10% estimate stats. The stats were different each time causing the incorrect execution plans to be created. The solution was to add hints to it and not rely on CBO using the stats for that table.

regards,
Billy Received on Thu Oct 22 1998 - 02:51:19 CDT