Re: Tuning PL/SQL

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sun, 18 May 2003 06:40:40 GMT
Message-Id: <pan.2003.05.18.06.40.38.849249@adelphia.net>

On Fri, 16 May 2003 14:01:56 +0100, Niall Litchfield wrote:

> "Rainer Herbst" <rherbst_@_rz.uni-potsdam.de> wrote in message
> news:3EC4CC40.2010400@_rz.uni-potsdam.de...

>> 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.

>
> Given not much help - you don't post any of the sql involved, I'd suggest
> the following 2 courses of action.
>
> 1. EXPLAIN all of the sql to see if you have any bad execution plans
>
> 2. run
> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
> your procedure
> ALTER SESSION SET EVENTS '10046 trace name context off';
> This will give a trace file (in UDUMP ) with what you have waited for and
> how long you waited so you can see where you are spending your time.
>
> HTH
To be able to look into the waits, you'll need one of the 3 things:

a) 9.2.0.3 TKPROF with WAITS=YES
b) TRCE utility downloadable from Metalink.
c) Somebody else to look into the file. Suggestion: http://www.hotsos.com

   They're good. They're very good. They're Cary Millsap good.

I don't work for Hotsos.

-- 
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds 

Received on Sun May 18 2003 - 01:40:40 CDT