Re: Performance decrease dramaticcaly by using statistics

Next time the program, SQL trace the session and then tkprof it. Better, have the developer responsible for the program to turn SQL tracing on in the program and then you may tkprof the trace file in the udump dir. There are a lot of options to sort by in tkprof, here's an example of what I use when I need to sql trace:

tkprof ora_trace_file.trc outputfile.txt sys=no explain=userid/password sort='(exeela,fchela,prsela)'

This will put the most offending statement from longest execution elapsed time first in the trace file.

It may be that explain plan is different now than it was earlier and may require a hint or a different hint in the worst performing statements.

HTH,
Pete's

"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 - 12:59:07 CST