Pages

Thursday, April 30, 2020

Time logging in PL/SQL procedures

I need to monitor how much time takes an execution of a PL/SQL procedure. The procedure name together with its execution start and end times are saved in VT_UPDATE_LOG table. For example, some procedure VT_UPDATE sequentially invokes four other procedures VT_SOA_UPDATE,VT_MOLIS_UPDATE, VT_UPDATE_SARS and VT_UPDATE_V_OPA_DEMOG. The time log includes the names of the main and nested procedures:

To each monitored procedure I added calls to auxiliary procedures - VT_LOG_START at the beginning and VT_LOG_END before the end:

create or replace procedure VT_update_sars as
...
begin
VT_LOG_START;
...
commit;
VT_LOG_end;
end;
/

VT_LOG_START and VT_LOG_END executes in their own autonomous transactions:

create or replace PROCEDURE VT_LOG_START  
IS PRAGMA AUTONOMOUS_TRANSACTION ;
name varchar2(30); 
BEGIN
name:=UTL_CALL_STACK.SUBPROGRAM (2)(1);
   insert into VT_UPDATE_LOG (PROCEDURE_NAME)
   values (name);
   commit;
END  ;

create or replace PROCEDURE VT_LOG_END  
IS PRAGMA AUTONOMOUS_TRANSACTION ;
 name varchar2(30); 
BEGIN
name:=UTL_CALL_STACK.SUBPROGRAM (2)(1);
   update VT_UPDATE_LOG set END_DATE=sysdate where PROCEDURE_NAME=name and END_DATE is null 
   and START_DATE=(select max(START_DATE) from VT_UPDATE_LOG where PROCEDURE_NAME=name and END_DATE is null);
   commit;
END  ;