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

Hi Karsten,

As far as I know the SYS objects are not analyzed (or should not be). So they live without statistics. For nearly everything to happen in the database Oracle needs these objects.

Now you come to a dead loop: if they need some action done by SYS they need statistics (belonging to SYS). To get statistics they need statistics (belonging to SYS)... They can only phase it out when they have hinted every occurring statement or when they can be sure that a non-hinting of a statement does not lead to a dead loop of this kind.

Martin

karsten_schmidt8891_at_my-deja.com wrote:
>
> 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 Fri Feb 11 2000 - 12:32:17 CST