Re: Statistics based optimizer usable?

pihlab_at_cbr.hhcs.gov.au writes in article <1994May2.094338.1_at_cbr.hhcs.gov.au>:
>
> In article <1994Apr23.161212.4900_at_deerwood.hanse.de>, georg_at_deerwood.hanse.de (Georg Rehfeld) writes:
> > Hi netters, especially you expecting oracles are interpretable
> >
> > yesterday again the same suspicious software, that forced us all many
> > times, attacked my (favorite) collegue once more, (s)he was near to
> > tears, and I could not help. May be, one of YOU has seen this before or
> > has a suggestion.
> >
> > The problem arises, when the default statistics based 'optimizer' is
> > used by doing an 'ANALYSE' command for all of our tables. It disappears,
> > if we force the rule based optimizer by including an 'OPTIMIZE=RULE'
> > statement in our 'init.ora' and reappears by commenting out this
> > statement.
> >
> > What happens is, the PARSING time for the statement increases from
> > somewhat like 0.3 seconds (rule based) to
> >
> > . .-. +---- +----
> > /| | | | |
> > | | | +--. +--.
> > | | | ) )
> > --- `-' ---' ---' seconds (more than 15 minutes to PARSE !!!!!!!)
> >
> > while the 'execute' and 'fetch' times are in good range (0.01 and 0.03 sec).
> >
> > Please remember, parsing means: translate SQL into a low level execution
> > plan, with an statistics based 'optimizer' one (I) would accept 1 second
> > to examine the data dictionaries for best choice; what, the hell, does
> > the oracle with the rest of time ???
> >
> > This seems to be another big bug in oracle's 7.x version --- or what are
> > we doing wrong?
>
>

I remember reading about a problem when you do an "analyze table" and a subsequent "drop statistics". ORACLE does, in some cases, use the (now nonexistent) statistics.....

The workaround for this is, as far as I remember, to drop and recreate the indexes of the table.

Willy

Willys Mail     FidoNet   2:2474/117  2:2474/118     Mail Only System
                CIS: 100020,3517       USR Courier HST dual standard
                willyk_at_kbigate.stgt.sub.org
                ->   No Request from 06.00 to 08.00 <-
======================================================================