Performance decrease dramaticcaly by using statistics

First of all, thanks for your hints.
Yes, all statistics is complete and up to date. I have been used your hints in combination with statistics and the performance is back to excellence. The same when I delete the statistics.
Of course I have to tune the application, but what I don't understand is that
Oracle always said to used the statistics to have better performance independent from what
the application do.
Could you tell me what the database exactly doing when the statistics is filled and which database resources the transactions used. I'm a DBA and first what I'm to find out is if the database wel tuned or not.  

Thanks,
Ruud

-----Oorspronkelijk bericht-----
Van: Paul Brewer [mailto:paul_at_paul.brewers.org.uk] Gepost op: vrijdag 22 februari 2002 20:20 Gepost naar: server
Discussie: Performance decrease dramaticcaly by using statistics Onderwerp: Re: Performance decrease dramaticcaly by using statistics

Are you sure your stats are complete and up to date?

If so, it looks like the app is tuned for the rule based optimizer. Three possibilities:
select /*+ RULE */
OPIMIZER_MODE=RULE
drop the statistics

Warning: This is a *short-term* fix - RBO will disappear...

HTH,
Paul

"Gijanto, Ruud" <RGijanto_at_ctgzorg.nl> wrote in message news:7C3B10C067C2D2119CE60020354473CE6806C7_at_SPICA...
> We have a Oracle Cliënt-application fully generated by Designer 2.1.2
> and a Databaseserver 8.1.7.2 on a IBM RS6000/AIX. The
cliënt-application
> call a user-exit on the RS6000 AIX-machine and written on c (.csql).
> The embedded sqls in the user-exit use a lot of table joins. A view is
> not use because the sql-statement will be build dynamically depends on
> the transactions job and a various parameter variables.
> OPTIMIZER_MODE = CHOOSE
> Without statistics is the elapsed time of a jobs/transactions
> approximately 1 minute.
> With statistics increase the elapsed-time to min. 25 minutes.
> I don't understand why this happened.
> Could someone help me with hints where i have to looking for to
analyze
> this problem.
>
> Thanks,
> Ruud Gijanto
> rgijanto_at_ctgzorg.nl
>
>
>
>
Received on Mon Feb 25 2002 - 03:57:32 CST