Re: How do you un-analyze tables?

"Hans Bos" <hans.bos_at_xelion.nl> wrote in message news:3b3b8172$0$14122$4d4ebb8e_at_read.news.nl.uu.net...

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?

I would like to *urge* anyone to get more familiar with the Cost Based Optimizer.
There are so many features which are not supported by RBO, like *Parallel Query*, *Function Based Indexes*, *Partitioning* to mention only a few, which means you are severly crippling yourself by persisting to use RBO, which can be deleted by Oracle any moment. During the years I have been using CBO, I have only seen very few instances of merge join cartesian. In allmost all of them either the statement was incorrect, or the tables were not in the proper order in the from clause. So, when asking such a generic question, the generic answer should be : learn to use the CBO, or else come up with specific examples. You also might want to know that Oracle is continually improving on CBO. I see many people using CBO in an one-time experiment and rejecting simply because it uses more full table scans. When they upgrade to a newer version they simply stick to the old belief the CBO is 'bad'

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Jun 28 2001 - 16:57:03 CDT