You can use any type of table or any data to see the rate of insert you are getting out of your Oracle RDBMS.   For this demonstration, we used a test table named INSERT_TEST and repeating insert of ever changing record sets.    You can change the scripts attached to satisfy your particular needs.

 

1.  Create table to hold insert records.

create table insert_test( var_a varchar2(100), var_b number(10,1));

2.  Insert a single record into your test table.

insert into insert_test VALUES(‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’,999999999.9);

3.  Determine the size of row inserted.

SQL> select avg_row_len from dba_tab_statistics where table_name=’INSERT_TEST’; AVG_ROW_LEN

———–        

112

SQL>

4.  Edit the following script, replacing the record size with the actual size you received in step 3 above.

SET SERVEROUTPUT ON

DECLARE  

l_loops           NUMBER := 100000;

 l_start           NUMBER;

 total_time number;

v_ct  number:=0;

v_insert_ct number:=1.1;

v_var_a varchar2(100);

v_mb number;

v_rec_ct number;

record_size number:=112;  

BEGIN

   l_start := DBMS_UTILITY.get_time; 

  FOR i IN 1 .. l_loops LOOP

  v_insert_ct:=v_insert_ct+1; v_var_a:=’AAAAAAAAAAAAAAA’||v_insert_ct; insert into insert_test VALUES(v_var_a,v_insert_ct);

v_ct:=v_ct+1;

if v_ct>99

then

commit;

v_ct:=0;

 end if;

   END LOOP;   

 DBMS_OUTPUT.put_line(‘Execution Time   : ‘ ||(DBMS_UTILITY.get_time – l_start)||’  ms’); DBMS_OUTPUT.put_line(chr(10));

select round(100000/((DBMS_UTILITY.get_time – l_start)/100),2) into v_rec_ct from dual;

DBMS_OUTPUT.put_line(‘Number of Inserts per second: ‘||v_rec_ct||’ records’);

DBMS_OUTPUT.put_line(chr(10));

select round((100000/((DBMS_UTILITY.get_time – l_start)/100)*record_size)/1024/1024,2) into v_mb from dual;

DBMS_OUTPUT.put_line(‘MB of Inserts per second: ‘||v_mb|| ‘ MB’);

END; 

 /

5.  The result set below is returned, showing the insert rate achieved by your Oracle installation.   NOTE:  Multiple things will impact your insert rate, so for additional question leave a comment.

Execution Time   : 513  ms

Number of Inserts per second: 19493.18 records

MB of Inserts per second: 2.08 MB

PL/SQL procedure successfully completed.

SQL>

Larry Catt