Q

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?

Hi. 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? Thanks!

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.

This was first published in March 2006

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

3 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close