I would like to measure the execution time of a stored procedure in Oracle. I have learned about the technique of writing an entry in a temporary logging table at the start and the end but am unable to use this technique.
Can you refer me to an open source/free tool with which I'm able to do this?
Thanks!
The answer depends on your environment you are using.
If you are using SQLPlus, you can enable a timer as follows :t
SQL> set timing on
Then, just execute your procedure, like :
SQL> exec my_procedure;
When the procedure will complete, a summary line will be displayed, like :
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.05
From within PL/SQL, you can use dbms_utility.get_time :
DECLARE
start_time pls_integer;
BEGIN
start_time := dbms_utility.get_time;
exec my_procedure;
dbms_output.put_line((dbms_utility.get_time - start_time)/100 || ' seconds');
END;
/
Should output something like :
3 seconds
PL/SQL procedure successfully completed.
Execution of the stored procedure's start /end time can be logged using
DBMS_UTILITY.get_cpu_time or DBMS_UTILITY.get_time
E.g.
CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
DBMS_OUTPUT.put_line('start time '||DBMS_UTILITY.get_time);
<your statement>
DBMS_OUTPUT.put_line('end time '||DBMS_UTILITY.get_time);
END;
There are of course other ways of finding the execution time using Profiler
Have a look at this as well
If you want to get details that can actually help you diagnose issues, I highly recommend dbms_profiler.
It is easy to use and provides statistics for every line in the pl/sql including number of executions, total time, min and max.
