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!

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: