Oracle measuring execution time stored procedure

Question

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!

Solution

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.

See this excellent explanation from Tom Kyte.

Comments

[APC]

Use Tyler Muth's logger library. It's the closest thing to an industry standard. Check it out