Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 Fri Feb 22 2002 - 13:20:20 CST