Log execution time for a procedure

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)
)
/

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.