PLSQL - measuring the duration of a procedure

Question

I have a procedure that runs every hour, filling out a table. There are a lot of records processed from the procedure, so each time it is executed, it takes about 12 ~ 17 minutes. Are you now, if there is a way (i.e., a Trigger) to record the duration of each execution (i.e., to a table)?

Answer 1

I do not know the trigger that will 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 need it for only a few procedures, this may be enough.

Share and enjoy.

Answer 2

I usually use a log table with a date or time column that uses the default sysdate / systimestamp value. Then I call an autonomous procedure that inserts records into certain places that excite me (starting / ending a procedure call after committing, etc.):

See here (look for my answer).

If you insert millions of rows, you can control when (how often) you insert into the log table. Again, see my example.

Answer 3

To add to the first answer, as soon as you have the start and end timestamps, you can use this function to turn them into a few milliseconds. It helps with readability, if nothing else.

 function timestamp_diff( start_time_in timestamp, end_time_in timestamp) return number as l_days number; l_hours number; l_minutes number; l_seconds number; l_milliseconds number; begin select extract(day from end_time_in-start_time_in) , extract(hour from end_time_in-start_time_in) , extract(minute from end_time_in-start_time_in) , extract(second from end_time_in-start_time_in) into l_days, l_hours, l_minutes, l_seconds from dual; l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000; return l_milliseconds; end;