Home » SQL & PL/SQL » SQL & PL/SQL » Finding how much time a procedure takes...without having access to the code of the procedure!
Finding how much time a procedure takes...without having access to the code of the procedure! [message #226833] Mon, 26 March 2007 13:39 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I got this question today from a person in QA department..he has to do load testing of a procedure and wants to find out how much time the procedure takes to execute.
The procedure gets fired when the application does something and an event happens...that depends on something done at the app level.. Now he does not have access to the code of the procedure , nor he has the permission to alter the code! the procedure does not calculate the time spent in its execution, nor does it produce any log file. Given this, is it still possible for this QA person to find how much time it takes fo rthe procedure to execute?

With thanks,
Nirav
Re: Finding how much time a procedure takes...without having access to the code of the procedure! [message #226906 is a reply to message #226833] Mon, 26 March 2007 21:32 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
I'm not sure why this is an expert question or what it involves PL/QSL.

SQL> SET TIME ON
SQL> EXEC PROCEDURE_TO_BE_TIMED;
SQL> EXIT

After "SET TIME ON" the prompt will include the time of day.
The duration of the procedure can be obtained by subtracting from the END_TIME the BEG_TIME.

HTH & YMMV
Re: Finding how much time a procedure takes...without having access to the code of the procedure! [message #226978 is a reply to message #226906] Tue, 27 March 2007 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The QA-people should not be interested in the time it takes to execute a single procedure, but the time it takes to perform a single functional action.
This can be timed using a wall-clock.
This is exactly the reason:
a) why they don't have access to the code
b) why you should have instrumented your code before sending it to QA.
Re: Finding how much time a procedure takes...without having access to the code of the procedure! [message #227105 is a reply to message #226978] Tue, 27 March 2007 06:13 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
go through this link. It explain what exactly you are after.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2468040431316
also search in the same website for dbms_profiler.

HTH
Previous Topic: Exceptions
Next Topic: SQL with and without "Parallel" option
Goto Forum:
  


Current Time: Sat Dec 03 18:20:15 CST 2016

Total time taken to generate the page: 0.08671 seconds