RE: CBO in 10g

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 14 Oct 2005 11:05:33 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050395BB50@usahm236.amer.corp.eds.com>

If my memory is correct and I am pretty sure it is the default database optimizer mode was CHOOSE starting with the introduction of the CBO with version 7.0 and Choose remained the default through version 9.2 (according to the Reference manual). With 10g Oracle is finally positioning the setting for the removal of the RBO.

IMHO -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Thursday, October 13, 2005 11:56 PM To: brian.peasey_at_gmail.com; Oracle-L
Subject: RE: CBO in 10g

According to:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ ch1124.htm#6092

Optimizer_mode defaults to CHOOSE, at least as far back as 8.1.6.

According to:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/init params146.htm#REFRN10145

Optimizer_mode defaults to ALL_ROWS, in 10.1.

What I think they are trying to imply in 'A' below, is that under 8i, with a default of CHOOSE, if no statistics exist, and if you don't use any features that force CBO (IOT, partitions, parallel query, etc) then the optimizer will fall back to RBO. In 10g, default is ALL_ROWS, which definitely implies CBO. Further, optimizer_mode=RULE is not supported in 10g. The good news is that 10g should automagically take care of stats collection for you.

But, the gist of the statements, while perhaps not written completely clearly, are I think, correct. The short summary is: 10g defaults to ALL_ROWS which implies CBO, even if stats are out of date or don't exist.
10g does NOT support optimizer_mode=RULE. (It's there, but not supported.)
10g should automate stats collection for you out of the box.

As to item 'B', I have not idea what specific bug they may be referring to, but, when going to a new release, it always makes sense to go straight to the most up-to-date patchset.

Hope that helps,

-Mark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BP Sent: Thursday, October 13, 2005 11:43 PM To: Oracle-L
Subject: CBO in 10g

Hi Guys,

I received this today from one of our developers, who in turn got it from Progress. Is this true? I thought CBO was default starting in 8i and in 10g stats are automatically collected.

N.B. 'Dataserver' is their product used to translate progress code to SQL. Brian Peasey

start quote------------

Be careful of Oracle 10g:
A) the default optimizer under 10g is now Cost-Based (CBO); it used to be Rules-Based (RBO) under 9i. If your database statistics aren't up to date then the CBO can easily choose table scans. We are in the process of writing a whitepaper on this and it should available via PSDN soon (use the above hyperlink).
Anyone on OpenEdge 10 against Oracle 10g should get the latest OpenEdge service pack as there was an issue related to Function Based Index usage that could cause unnecessary Table Scans.

B) Try to upgrade to at least Oracle 10g (10.1.0.4) - there is an Oracle bug that intermittently generates an Oracle 600 error

Simon Epps
DataServer Product Manager
Progress Software
--------end quote
--

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

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

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 14 2005 - 10:10:50 CDT