oracle display for every stored procedure the execution time

Question

Hi all.

I'm working on a stored procedure. Inside this one, there are many call to the other stored procedures. There are a bunch of them.

I was wondering if there is a option to be able to have the execution time of every stored procedure involved, every function (with a start and end time, ior something like that).

The idea is that I need to optimise it and I should touch every part, and since I not sure where is the longest execution time, is a bit difficult. And after a modification I would like the see the hole process if it's shorter or not. If I call the procedure from unix, using sql plus, I have no log. If I call it from TOAD, it's blocked until the end.

Any idea?

I'm not a dba, so I don't have many rights on the database, I'm just a regular user.

Thanks for any advice.

C.C.

Answer 1

I have seen logging procedure that was transaction independent (PRAGMA AUTONOMOUS_TRANSACTION;) and was called from main procedure. It saved in funtime_log table:

• current time (wall clock),
• sequential number,
• thread (session) id,
• and text (eg. name of procedure)

This way you can select all events from one session ordered by sequential number and see where the time differs most. In production environment you can simply make this function do nothing to disable logging.

Answer 2

If you are using Oracle 11g you should check out the built-in Hierarchical Profiler. It does pretty much exactly what you're proposing to do. Unfortunately rights on DBMS_HPROF are not granted to PUBLIC by default, so you'll need to ask your DBA to grant you EXECUTE privilege. As it's to help you with tuning I'm sure they be only too happy to comply.