Skip to Main Content
  • Questions

  • How to measure the Performance of a Stored Procedure

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kalpesh.

Asked: February 07, 2001 - 12:16 pm UTC

Last updated: March 12, 2001 - 1:21 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi Tom ,
I have some Stored Procedures in my database.
I want to know the some methods so that I can measure the performance of those Stored Procedures.
Actually I want time taken by it or CPU usage or disk scan or table scan.
I want all above values into some variables so that I can monitor it using some monitoring tool.

Thanks in Advance
Kalpesh


and Tom said...

There are two tools you want to use.

1) </code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76992/ch14_str.htm#1018 

describes sql_trace and tkprof.  sql_trace will trace the execution time of the sql in your procedure.  It can show you physical/logical IO's, cpu vs elapsed time, number of times executed, rows operated on and so on.  You can use tkprof to either create a report or a sqlplus script file to load the stats into the database.


2) DBMS_PROFILER is a plsql source code profiler built in the 8.1.5 and up database.  In 8.1.5.0, the installation scripts are not shipped with the server (should have been fixed in 8.1.5.1).  You can either use metalink and refer to note 1076139.6 to get them or contact support.

then see:
http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76936/dbms_pro.htm#1003424 <code>
....


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Performance Measurement of Stored Procedure

Kalpesh Patel, March 12, 2001 - 1:21 pm UTC

Thanks Tom for such a useful information.
It is really a best way to find out where the problem is in our stored procedure regarding performance issues.
The best thing is it is actually showing the query which is taking lot of execution time in Stored Procedure.
Thanks a lot.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library