Newsgroups: comp.databases.oracle
From: georg@wmdhh.wmd.de (Georg Rehfeld;WMD GmbH)
Subject: Statistics based optimizer usable?
Message-ID: <1994Apr26.224757.14945@wmdhh.wmd.de>
Sender: news@wmdhh.wmd.de (News System)
Nntp-Posting-Host: ix1
Organization: WMD GmbH, Hamburg
X-Newsreader: TIN [version 1.2 PL0]
Date: Tue, 26 Apr 94 22:47:57 GMT
Lines:      61


Hi netters, especially you expecting oracles are interpretable

My first post of this seems to be lost on the way to the net, if
not and you read this twice I apologize.

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 'ANALYZE TABLE <table> COMPUTE STATISTICS;' command for
all of our tables.  It disappears, if we force the rule based optimizer
by including an 'OPTIMIZER_MODE=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?

We use RDBMS V 7.0.15.4.0 on a RS6000 - 360,
the statement used is
 select *
 from   myview
 where  col like 'xyz%';
and the view 'myview' is a join of 8 tables with equi-joins between
primary and foreign key columns. All primary key columns have the
primary key constraint and are therefore uniquely indexed, all foreign
key columns have the foreign key constraint and are indexed additionally
non-unique. The select clause of the view has about 60
columns/expressions, the expressions are of nvl() and decode() type.

What I'm interested in:
- has one of you experienced similar results?
- has on of you a solution or suggestion?
- has one of you used the statistic based optimizer with good results?
- are there known bugs/limitations in using the statistic based optimizer?

Thanks

Georg
--
 ___   ___
| + | |__   Georg Rehfeld, WMD GmbH, D-22453 Hamburg, Vogt-Cordes-Damm 10
|_|_\ |___  e-mail: georg@wmdhh.wmd.de            phone: +49-40-58958-225