Re: When does performance worsen when going from RULE to COST

Hi Martin,

IMO consistency (== predictable performance) is more important than a 'smart' optimiser that works 99% of the time. So, I agree on your recommendation not to use CBO.
But what are you going to do once RBO is phased out ? Oracle has been talking about that for years. Fortunatly, they did'nt do it until now, but if they do ?????

Karsten

In article <389EA7BD.DF7040F4_at_0800-einwahl.de>,   Martin.Haltmayer_at_0800-einwahl.de wrote:
> Hi Jaap,
>
> my experience tells me to start up every instance with
"optimizer_mode =
> rule" if you are running a OLTP system serving dialogue responses.
>
> In 7.3.4.3 we found strange behaviour when doing updates or deletes
with
> CBO. Try the following example:
>
> Have a big table b and a small table s, both with one column. Have a
> primary key on each of these columns. Analyze both tables. Now delete
> all the rows of b that appear in s. Sometimes it uses the full table
> scan on s and using the unique index of b to find the rows to delete,
> sometimes it does a full table scan on b and a nested loop with the
> primary key on s. If you have a hint --+ index (s primk) then your
> chances are better. RBO will pick the correct path (FTS on s).
>
> In 8i there is a small bug: If you have your instance not started with
> this init.ora setting and you try to fully export an (even empty)
> database, the export will be stuck when exporting some post-table
> things. Starting the instance with sql_trace = true revealed that it
was
> an access to some SYS object which was taking hours (in an empty
> database). The export went like a charm when we started with RBO.
>
> Bottom line: do not use CBO as a default until 8.1.5 included.
>
> Martin
>
> "Jaap W. van Dijk" wrote:
> >
> > Hi g's,
> >
> > When you want to change a database from rule based to cost based
you're
> > naturally are going to be worried somewhat about the general and
query
> > specific effects
> >
> > My question: Are there general causes for degradation people
stumble upon
> > when doing this change? This of course under the assumption that
every table
> > has been analyzed (ANALYZE TABLE <name> COMPUTE STATISTICS).
> >
> > For example (one I've seen often): the rule based optimizer uses an
index,
> > and the cost based doesn't under the false assumption that a
literal in the
> > WHERE clause is one of the evenly distributed values of a column,
where in
> > fact it is a very rare value (e.g. the literal 'F' for the column
GENDER for
> > a personnel table in a database used by the army). Of course this
can be
> > gotten rid of by gathering histogram statistics for this column,
but solving
> > this particular example is not the issue here.
> >
> > Jaap.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 08 2000 - 07:28:51 CST