Some system require the insertion of large amounts of data in as short a period of time as possible. In this cases, you must be able to measure actual physical time of insertion per give number of records or number of records inserted per second.   The following block allows you to perform this action on a give oracle operation.

We will use the following anonymous block to track our processing time:

SET SERVEROUTPUT ON  DECLARE      v_loops          NUMBER := 1000000;    v_start_time     NUMBER;    v_total_time  NUMBER;    v_ct    NUMBER :=1;    BEGIN

— Time we started our process.    v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP   < Oracle Action:  Insert, function call, procedure call, etc >   v_ct:=v_ct+1;   commit;   END LOOP;       –Calculate the time in seconds     v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;           DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  END;  /     Example Execution:

1.  Connect as the user scott and create a table test_data

SQL> connect scott/tiger@orcl Connected. SQL> create table test_data(user_no number, fname varchar2(30), lname varchar2(30));

Table created.

SQL>

2.  Execute your block with < Oracle Action …> replaced with an insert statement for test data.

SET SERVEROUTPUT ON  DECLARE      v_loops          NUMBER := 1000000;    v_start_time     NUMBER;    v_total_time  NUMBER;    v_ct    NUMBER :=1234;    BEGIN

— Time we started our process.    v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP   insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’);   v_ct:=v_ct+1;   commit;   END LOOP;       –Calculate the time in seconds     v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;           DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  END;  /      Results:    SQL> SET SERVEROUTPUT ON SQL>  DECLARE   2   3     v_loops          NUMBER := 1000000;   4     v_start_time     NUMBER;   5     v_total_time            NUMBER;   6     v_ct                            NUMBER :=1234;   7   8   BEGIN   9  10     — Time we started our process.  11     v_start_time := DBMS_UTILITY.get_time;  12  13    FOR i IN 1 .. v_loops LOOP  14  15  insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’);  16    v_ct:=v_ct+1;  17    commit;  18    END LOOP;  19  20     –Calculate the time in seconds  21      v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;  22  23      DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  24     END;  25   / This process took a total of : 86.45 seconds to complete

PL/SQL procedure successfully completed.

SQL> SQL>

3.  The results show a insert rate of 86.45 seconds for 1 million records or 11,567.38 records per second.

Larry Catt