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 ;
No comments:
Post a Comment