Log execution time for a procedure
I need to somehow log the execution time for two procedures every time they are called and save that data (in a table?). Do you know of a way to isolate tracing to a specific procedure in order to do this?
It really depends on what you are trying to do. You can use DBMS_APPLICATION_INFO if you are running a long procedure and just want to see what it's doing. If you really need to log run times, you can write a simple program to do that.
First, create a log table:
CREATE TABLE PROC_LOG ( PROC_INSTANCE NUMBER NOT NULL, PROC_NAME VARCHAR2(100) NOT NULL, IN_TIME TIMESTAMP(6) NOT NULL, OUT_TIME TIMESTAMP(6), COMMENTS VARCHAR2(1000), PRIMARY KEY (PROC_INSTANCE) ) /And a sequence:
CREATE SEQUENCE PROC_INSTANCE /
In the table, proc_instance is a sequence generated primary key so that you can identify unique runs of your procedure. Proc name can be the actual procedure you are running or a made up process name. In_time is the date and time that the run started and out_time is the date and time it ended. Comments are an optional clear text field.
And now a log package:
CREATE OR REPLACE PACKAGE proclog_pkg AS FUNCTION init_log( p_proc_name IN VARCHAR2, p_comments IN VARCHAR2 DEFAULT NULL ) RETURN proc_log.proc_instance%TYPE; PROCEDURE end_log( p_proc_instance IN proc_log.proc_instance%TYPE ); END; /
Init_log starts a record and end_log finishes it. See the package body:
CREATE OR REPLACE PACKAGE BODY proclog_pkg AS FUNCTION init_log( p_proc_name IN VARCHAR2, p_comments IN VARCHAR2 DEFAULT NULL ) RETURN proc_log.proc_instance%TYPE AS v_proc_instance proc_log.proc_instance%TYPE; BEGIN INSERT INTO proc_log (proc_instance, proc_name, in_time, comments) VALUES ( proc_instance.nextval, p_proc_name, localtimestamp, p_comments ) RETURNING proc_instance INTO v_proc_instance; RETURN v_proc_instance; END; PROCEDURE end_log( p_proc_instance IN proc_log.proc_instance%TYPE ) AS BEGIN UPDATE proc_log SET out_time = localtimestamp WHERE proc_instance = p_proc_instance; END; END; /
You run it like this:
DECLARE v_proc_instance proc_log.proc_instance%TYPE; i NUMBER := 0; i_am_done BOOLEAN := FALSE; BEGIN -- Start logging v_proc_instance := proclog_pkg.init_log('My Package Name', 'This is a comment' ); -- Run some random code LOOP EXIT WHEN i_am_done; i := i + 1; i_am_done := i > 10000; END LOOP; -- End logging proclog_pkg.end_log( v_proc_instance ); END; /
Now you can select from the proc_log table and see your values.
If you want it to always commit, you can modify the procedures by using pragma autonomous_transaction and always commit in this package.