Statistics based optimizer usable?

From: Georg Rehfeld <georg_at_deerwood.hanse.de>
Date: Sat, 23 Apr 1994 16:12:12 GMT
Message-ID: <1994Apr23.161212.4900_at_deerwood.hanse.de>


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?

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,  D-20535 Hamburg, Jordanstr. 8,  [49] (40) 2518356
|_|_\ |___, georg_at_deerwood.hanse.de
Received on Sat Apr 23 1994 - 18:12:12 CEST

Original text of this message