| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO in 10g
Another gotcha with 10g automagic collection is there are some issues.
By default 10g will collect system stats which was not availAble with 9i and at startup apparently which would of course in most cases be an issue because system utlization at startup is not at all representative of system util during normal activity. This would also affect the execution plan chosen by the CBO and not necessarily in a good way.
I am sure that someone in this group can give better details of that issue. :)
-----Original Message-----
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
To: brian.peasey_at_gmail.com <brian.peasey_at_gmail.com>; Oracle-L <oracle-l_at_freelists.org>
Sent: Thu Oct 13 23:56:11 2005
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------------
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
--
BEGIN-ANTISPAM-VOTING-LINKS
Spam: https://dohsmsi01.doh.state.fl.us/canit/b.php?c=s&i=48046967&m=a841b2c1da34 Not spam: https://dohsmsi01.doh.state.fl.us/canit/b.php?c=n&i=48046967&m=a841b2c1da34 Forget vote: https://dohsmsi01.doh.state.fl.us/canit/b.php?c=f&i=48046967&m=a841b2c1da34END-ANTISPAM-VOTING-LINKS
------------------------------------------------------
 |
 |