>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@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Sergey
Popov
Sent: 12. júlí 2006 14:26
To:
oracle-l@freelists.org
Cc: Alex Gorbachev;
ax.mount@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@gmail.com>
wrote:
2006/7/12,
Sergey Popov <sspopov@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
