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