Re: RBO changes plan from 8i to 9i

Alex,

Hard to disagree on one hand but on the other hand CBO gives you much more options available to tune and eventually to come up with execution plan no worse than RBO. Siebel with some 2300 tables and 11500 indexes created on them gives RBO a lot of opportunities to use these indexes no matter if it isn't the best way to access the data (my "favorite" table is the one with 61 indexes on it).

Every time I have a performance related call from the SIebel app support  guys I test their SQL in both RBO and CBO. So far in every single case I was able to come up with a CBO plan that outperforms RBO by at least 50%.

It is a known fact that a small percentage or queries will run slower after RBO-to-CBO migration. However, this is where doing it on a test system with proper production workload modeling helps to identify most of the problem plans before moving on prod.

I've never said that CBO is the magic "fast_response=true" parameter :-)

Sergey

On 7/12/06, Alex Gorbachev <gorbyx_at_gmail.com> wrote:
>
> 2006/7/12, Sergey Popov <sspopov_at_gmail.com>:
> > With CBO making much better decisions on execution plan...
>
> Very optimistic statement. :-) CBO is supposed to make them better ON
> AVERAGE providing_certain_conditions_are_met.
>
> Another thing where CBO is far from RBO (and I doubt it will be ever
> able to reach it) is execution plan stability. With RBO you are almost
> sure that you plans stay intact even if they are suboptimal. "Almost"
> becuase there are some minor issues like index creation order.
>
>
>
> --
> Best regards,
> Alex Gorbachev
>
> http://blog.oracloid.com
>

--
http://www.freelists.org/webpage/oracle-l