Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with PL/SQL procedure
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