Statistics based optimizer usable?
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.deReceived on Sat Apr 23 1994 - 18:12:12 CEST