Re: Statistics based optimizer usable?

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?

Both you and Oracle could be doing everything right. The fact that it's slow doesn't mean there is a bug; just that it's doing a LOT of work.

See below.

>
> 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?

I was told that there were problems with the statistics based optimiser but they were supposedly fixed in 7.0.15+. We're still using 7.0.13 with the rule based optimiser only.

Then again, an 8 way table join is something to have nightmares about.

What you could do is EXPLAIN the script and then put in HINTS on the VIEW so that the optimiser restricts the parsing work. This is my preference.

OR simply turn off the statistic based optimiser for that script with a HINT.

If this is a regularly used script then you should get some benefits by having the parsed script already parsed once inside the SGA.

Another way to do the script is to store it as a package procedure in the database (pre-parsed) and simply pass in the 'XYZ%' search value but I'm not sure how you would handle multiple rows returned.

>
> Thanks
>
> Georg
> --
> ___ ___
> | + | |__ ' Georg Rehfeld, D-20535 Hamburg, Jordanstr. 8, [49] (40) 2518356
> |_|_\ |___, georg_at_deerwood.hanse.de
 

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************