Re: Tuning PL/SQL Procedures

"GeoPappas" <PappasG_at_gmail.com> wrote in message news:1131031547.194467.279820_at_f14g2000cwb.googlegroups.com...
>I am familiar with the ability to tune a SQL statement, but is there a
> way to tune a procedure?
>
> For example, we have a procedure in PL/SQL that is a few hundred lines.
> I would like to test the procedure and capture a benchmark. I would
> then like to try various things to try and improve performance.
>
> Is utlbstat/utlestat the best way?
>
> Are there other ways?
>
> FYI: Using Oracle 10g on Solaris 2.9
>

Take a look at the dbms_profiler package.

This allows you to capture the number of visits to, and time spent in, each line of the pl/sql. So it tells you which bits of code need tuning, and lets you know if you've got any code paths that haven't been tested by your data.

The overhead while running a test is, obviously, rather large.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
Now available.

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 22nd Oct 2005