Re: Optimizer

In article <958422110.13305.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> I don't agree with this approach. It is quite simple to analyze your
 tables
> every week by means by dbms_job.

It has been my experience that you have to be careful when analyzing tables. If you don't analyze tables when they have volumes similar to the volumes during the queries on that table, Oracle may perform worse than if you did analyze at all. So if you have tables that fluctuate wildly and your DBA don't know the proper table sizes during production runs it may
be better to guarantee the what path a query will take.

> The rule based optimizer will be taken out soon, with the next major
> release, and I promise you, you are going to have fun, when all your
 queries
> have awful performance.
> It's better to prepare now, than to sit back and wait for major
 disaster
> occurring.

Oracle has been threatening to remove the rule based optimizer for several releases. I imagine it will be around for several more. Once it is gone queries with the rule hint will just use the cost optimizer. I hear oracle is rewriting the cost optimizer for the next release. If it turns out to be a dog I can't see them getting rid of the rule based optimizer.

> Also, the cost based optimizer is much less dependent on the 'correct'
> sequence of tables and other expressions.
> Sticking to the rule-based optimizer is definitely undesirable.

Yes this is true, but the cost based optimizer get confused too. You will often different explain plans when you change the order of tables. Basically you will have to tune all poor performing queries (whether they are cost or rule). My personal preference is to get an explain plan using a rule hint just so I can see which indexes can be used. I then rewrite the query using index, use_nl and other hints.

>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 15 2000 - 00:00:00 CDT