RE: RBO changes plan from 8i to 9i

>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%.
 

The problem is that 50% better is nothing compared with 10-100 times worse which is typical for CBO. An increase from 0.1s to 1 minute is felt particulary by OLTP. It is not such bad an increase from 12h to 24h in dw (also it is not good either)      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sergey Popov Sent: 12. júlí 2006 14:26
To: oracle-l_at_freelists.org
Cc: Alex Gorbachev; ax.mount_at_gmail.com
Subject: 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
	

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer

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