PLSQL - Measure the execution duration of a procedure

Question

I have a procedure that runs every one hour populating a table. The records handled from the procedure are many so it takes approximately 12~17 mins each time it is executed. Do you now if there is a way (i.e. trigger) to record the duration of each execution (i.e. into a table)?

Answer 1

I don't know of a trigger that would allow this to be done automatically. One way to do this would be something like

PROCEDURE MY_PROC IS
  tsStart  TIMESTAMP;
  tsEnd    TIMESTAMP;
BEGIN
  tsStart := SYSTIMESTAMP;

  -- 'real' code here

  tsEnd := SYSTIMESTAMP;

  INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME)
    VALUES ('MY_PROC', tsStart, tsEnd);
END MY_PROC;

If you only need this for a few procedures this might be sufficient.

Share and enjoy.