Re: How do you un-analyze tables?

Sybrand,
  Thanks for reply pointing out the politics of CBO. My problem however is more fundamental.
Which optimiser is CHOSEN by the system at runtime. The Oracle system (the system not me) has to make a decision at some point about which optimiser it is going to use. Here are the facts,
Oracle ver - 8.1.5.
OS ver Sun Solaris - 2.8
Application - Oracle Discoverer.
The init.ora parameter optimizer_mode is set to CHOOSE as recommended by Oracle. Some queries from Discoverer choose RULE based some choose COST based; the selection is arbitrary as far as I can see. The difference between the two optimisers in some cases is downright spectacular! For example, one query makes the decision to use the CBO, it takes over three (3) hours to run. When, as an exercise, I add a hint to the query /* + RULE */ it only takes seconds (19.37 secs) to run; these figures can be taken as typical. It is the same for other queries the other way round, that is they run much faster with the CBO.
What I wish to know is why can Oracle not select the 'correct' optimiser when the results are to obvious?

George Barbour

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tjn9tcdci70a14_at_beta-news.demon.nl...
>
> "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 Fri Jun 29 2001 - 02:59:25 CDT