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