Re: cbo - rbo

mkoster <member_at_dbforums.com> wrote in message news:<1903120.1034085422_at_dbforums.com>...
> hi,
>
> our database (8.1.7) run with optimizer_mode=rule. i have analyze the
> tables und indizes and want to change the optimizer_mode=all_rows.
>
> has someone experiences with it ???
>
> thanks
> marcus

If you are an OLTP you might want to consider using FIRST_ROWS instead to more heavily weight the indexes. The default setting of CHOOSE will use ALL_ROWS when statistics exist on the referenced tables and RULE when no statistics exist.

We ran run our main OLTP cost based since version 7 and in general the CBO does pretty good, but when it is wrong it is very wrong and it is 'always' on a critical query. A little thought and a couple of hints can usually make everything right.

We imbedded into many of our applications a check againt an optimizer setting table that the logic used to alter the optimizer setting for a session running a specific task into RULE. This type of logic allows you to switch a poorly performing process back and forth between RULE, FIRST_ROWS, and ALL_ROWS without changing the code except for the initial routine call insertion into the code. This can buy you time while you figure out the best plan which may lie somewhere between the RULE and CBO developed plans selected by the optimizer and might take a hint to lock in.

If you go with all rows you can modify the optimizer_index_cost_adj and optimizer_index_caching parameters to dynamically change the weighing of the index by the CBO on a session by session level. If you use a feature similar to what I described above you could provide yourself a fairly dynamic SQL tuning tool for use with the switch over.

Otherwise just expect that a couple of tasks may go to lunch. But with choose if too many jobs go to lunch at the same time you could just drop all the statistics to switch back.

Just some thoughts.
-- Mark D Powell -- Received on Tue Oct 08 2002 - 15:34:14 CDT