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 -> Re: Performance problem with PL/SQL procedure

Re: Performance problem with PL/SQL procedure

From: AnaCDent <anacedent_at_hotmail.com>
Date: Thu, 15 Apr 2004 17:55:17 -0700
Message-ID: <T9Gfc.39377$U83.15010@fed1read03>


Sony Jose wrote:
> Hi,
>
> I have some stored procedures that are pretty time consuming. (They
> have been optimized to the maximum extend - very little to be done on
> that now).

If you believe that, then I have some high quality land I can sell you real cheap.

These procedures need to be executed repeatedly for a range
> of input parameters (batch processing from unix cron job).
>
> In order to work around this situation, we designed the cron job to
> create multiple background processes accessing the stored procs (each
> for a different range of inputs). Our assumption was that, this way,
> the processing time will be less than that for using a single unix
> process to invoke the procedures for the entire range of input.
> However, we are finding that the processing time is actually the same
> in both cases.
>
> Could someone please tell us whether there's a solution for this
> (maybe we need to change some settings somewhere?). Or is this how it
> works in Oracle? (We're using 7.3.4, by the way.) Any help will be
> appreciated.
>
> Regards,
> Sony Jose.

enable SQL_TRACE just prior to running the procedure Run your procedure
Run the resultant trace file thru TKPROF Look at the output file to see where the procedure is actually spending its biggest chunks of time. TUNE those SQL statements.

Stop guessing at what the problem might be. Collect FACTS.
Act to reduce the bottlenecks. Received on Thu Apr 15 2004 - 19:55:17 CDT

Original text of this message

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