Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Performance decrease dramaticcaly by using statistics

Performance decrease dramaticcaly by using statistics

From: Gijanto, Ruud <RGijanto_at_ctgzorg.nl>
Date: Mon, 25 Feb 2002 11:08:05 +0100
Message-ID: <7C3B10C067C2D2119CE60020354473CE68A1A5@SPICA>


First of all, thanks for your hints.

Could you tell me more specificly how to use your hints as a DBA. What I d'nt understand is that Oracle always said to used the statistics to have better performance independent from what the application doing. 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: empete2000_at_yahoo.com (Pete's) [mailto:empete2000_at_yahoo.com] Gepost op: vrijdag 22 februari 2002 19:59 Gepost naar: server
Discussie: Performance decrease dramaticcaly by using statistics Onderwerp: 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 Mon Feb 25 2002 - 04:08:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US