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: Tuning PL/SQL

Re: Tuning PL/SQL

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 16 May 2003 14:01:56 +0100
Message-ID: <3ec4e142$0$29718$ed9e5944@reading.news.pipex.net>


"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

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Fri May 16 2003 - 08:01:56 CDT

Original text of this message

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