Re: RBO changes plan from 8i to 9i
- From: "Sergey Popov" <sspopov@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Jul 2006 10:26:21 -0400
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@xxxxxxxxx> wrote:
2006/7/12, Sergey Popov <sspopov@xxxxxxxxx>:
> 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
Other related posts:
