Re: How do you un-analyze tables?

Niall Litchfield <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3b3b3d59$0$8511$ed9e5944_at_reading.news.pipex.net...
> 1. analyse <object> delete statistics;
>
> 2. There are many many reasons why CBO might make the wrong decisions.
 these
> include , but are not limited to
>
> a) too small or unrepresentative a sample,
> b) skewed data distributions,
> c) poorly chosen init.ora parameters (for example
 db multi block read count)
> d) poor indexing of the data.
>
> You should also be aware that the rules used for RULE optimisation do
 not
> take any account of many of the nice new features introduced in Oracle
 8 &
> 8i. for example I don't believe you'll ever get Oracle to use a
 materialized
> view under RULE.
>
> HTH
e) poor query optimizer

We have had similar expiriences.

Sometimes the optimizer assumes, according to the explain plan, that the result will only be 1 row. In that case it uses a really stupid plan (e.g. a carthesian join).
In our case the real query had more then one result, so that query ran in 15 min in stead of a few seconds (in rule based mode).

After analyzing some more columns, that particular query ran ok, but now others were running slowly (in our system queries can be dynamicly generated, so we don't know the precise queries in advance).

So now we are using the rule based optimizer.

I would think that the cost base optimizer (which has more information then the rule based optimizer) should result in at least the same performance as the rule based optimizer.

Greetings,
Hans Bos.

PS.
Does someone know a way to prevent oracle from perfoming carthesian joins? Received on Thu Jun 28 2001 - 14:11:55 CDT