| Measuring execution time of a procedure [message #47911] |
Thu, 30 September 2004 21:53  |
shahaness
Messages: 8
Registered: July 2004
|
Junior Member |
|
|
I want to measure the execution time of a PL/SQL procedure.
Basically I want to know the difference in execution times with and without a certain block of code.
I tried to extract time from Sysdate but it gives time in SECONDs.I want the precision upto MILLISeconds.
Is there any way to do this?Any performance measurement tool?
Thanks
|
|
|
|
| Re: Measuring execution time of a procedure [message #47914 is a reply to message #47911] |
Fri, 01 October 2004 00:01  |
Dave Pember
Messages: 1
Registered: October 2004
|
Junior Member |
|
|
Hi,
You can use dbms_utility.get_time to retrieve 100ths of seconds or the TIMESTAMP data type which can return fractions of seconds to 3 decimal places.
You can 'profile' your pl/sql code using the DBMS_PROFILER package. Basically start the profiler in your session, run the code, stop the profiler and look at your results. There is a standard report provided profrep.sql. Profiler shows for each line of code the number of times executed, max execution time and average execution time.
Dave
|
|
|
|
| Re: Measuring execution time of a procedure [message #47916 is a reply to message #47914] |
Fri, 01 October 2004 03:50 |
shahaness
Messages: 8
Registered: July 2004
|
Junior Member |
|
|
Thanks Dave
1.dbms_utility.get_time is giving output in 5 digits
like 24023 and 15631 .
Can't dare to say it is in milliSeconds:) since the processing of records hardly takes any time(may be 2 seconds max)
2.Don't know how to use Timestamp .
3.Yet to try the DBMS_Profiler .But I have seen the report it generates(in some online tutor).
I think it does not give the execution time.
May be I will continue to use dbms_utility.get_time.
|
|
|
|
| Re: Measuring execution time of a procedure [message #47926 is a reply to message #47916] |
Fri, 01 October 2004 14:14 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> 1. dbms_utility.get_time is giving output in 5 digits like 24023 and 15631.
These are hundredths of a second.
> 2. Don't know how to use Timestamp.
Find out how to use Timestamp?
> 3.Yet to try the DBMS_Profiler. But I have seen the report it generates(in some online tutor).
> I think it does not give the execution time.
Yes it does. The easiest way to use it is to get hold of a copy of TOAD, PL/SQL Developer etc, then it's simply point and click, run your procedure, browse the GUI output.
|
|
|
|
| Re: Measuring execution time of a procedure [message #47930 is a reply to message #47916] |
Sat, 02 October 2004 13:08 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
DECLARE
k_start_time CONSTANT TIMESTAMP := SYSTIMESTAMP;
BEGIN
DBMS_LOCK.SLEEP(2);
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP - k_start_time || ' processing complete.');
END;
/
+000000000 00:00:02.042517000 processing complete.
PL/SQL procedure successfully completed.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
