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

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.
Received on Mon Feb 07 2000 - 05:08:45 CST