Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Amit.

Asked: February 04, 2016 - 1:16 pm UTC

Last updated: February 04, 2016 - 5:16 pm UTC

Version: 12C

Viewed 10K+ times! This question is

You Asked

Hi Everyone,
I would like to have total execution time of a procedure in minutes, could you please advise how to do so?

Regards,
AS

and Chris said...

Start your stopwatch, run the procedure and stop it again when it finishes!

But seriously, you just need to record the time it started, the time it finished and find the difference. How you do this depends upon your environment. And you haven't told us what you're using.

In SQL*Plus you can set timing on:

SQL> create or replace procedure p as
  2  begin
  3    dbms_lock.sleep(5);
  4  end;
  5  /

Procedure created.

SQL> set timing on
SQL> exec p;

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.28


Or within PL/SQL you can call dbms_utility.get_time:

SQL> declare
  2    t1 pls_integer;
  3  begin
  4    t1 := dbms_utility.get_time;
  5    p;
  6    dbms_output.put_line((dbms_utility.get_time - t1)/100 || ' seconds');
  7  end;
  8  /
5 seconds

PL/SQL procedure successfully completed.


Or you could log to a table, or use the profiler:

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

If you want more information about a specific environment, you'll have to tell us what you're using.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library