RE: CBO in 10g
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <brian.peasey@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Oct 2005 23:56:11 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
