Re: Migrating to cost based optimizer

On 23 Sep 2006 01:37:59 -0700, hasta_l3_at_hotmail.com wrote:

>Dear group,
>
>Our applications are supporting Oracle 8.1.7 and Oracle 9, for many
>customers with varying level of activity.
>We use the rule based optimizer to ensure the stability of the sql
>statement's execution plans.

With that argument the only car would still have been the T-Ford
>
>We are thinking of moving to the cost based optimizer, at least in
>Oracle 10.

Premium support for 9i stops in July 2007. I would hurry!
>
>Would you have a strategy to recommand ?

do not use ANALYZE, but use dbms_stats
ALWAYS set up a stats table.
Make sure you gather system stats using dbms_stats, or change optimizer_index_caching and optimizer_ind_cost_adj from their defaults. The defaults favor full table scans.

Try to understand CBO and the plans CBO produces, do NOT start tweaking the statement until you go the RBO plan back. CBO often produces MUCH better plans.

Use *generic* hints only.

>
>What is the first Oracle release with a cost based optimizer working
>reasonably well ?
>

8.1.7.4

>How do you check the performances of your applications across oracle
>releases and customers with that optimizer ?
>
>Thanks.

Isolate important statements which run often. Run them in sql*plus with set autotrace on explain stat.
Compare the output.

--
Sybrand Bakker, Senior Oracle DBA