Re: Tuning PL/SQL

On Fri, 16 May 2003 13:32:16 +0200, Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote:

>Hi *,
>
>is there any way to tune the execution of a PL/SQL procedure?
>
>Here is what I get:
>- a procedure with app. 1200 lines of code
>- a quite old SUN station running Sun-OS 5.7
>- Oracle 8.1.7
>- the procedure is in the shared area
>- the procedure does not read any big tables,
> just some lookup-tables which are in the
> db block buffer
>- the procedure returns after 5-8 seconds which is definatly
> not satisfying the management (and the devoloper, too)
>- the hitratio for procedures is .99
>
>TMHO, 5-8 seconds is much to long, but what could I do?
>
>Thanks in advance!
>Rainer

Try the init parm PROC_EXECUTION_SPEED=FAST

Seriously, there is no silver bullet here. (Even if there were, you haven't given enough info to know it). Of course it is possible to tune the performance of a PL/SQL procedure. It's just a program, and you tune it like any other program. Look at that code with a programmer's eye. Are there loops? Are there things going on inside the loops that don't need to be inside the loop -- especially things like the same query -- yeilding the same result set -- being executed multiple times? Sit down with the developer and go thru the code line by line. Question everything . . "why are you doing this?", "Why are you doing this here and not somewhere else?" "You're deriving this value multiple times -- why not derive it once and save the value in a variable?"

Pull all of the sql statements out and execute them individually from sql-plus to see what the response is -- to see if any of them are individually misbehaving. Received on Fri May 16 2003 - 07:56:10 CDT