insert performance on NT and Solaris

From: Angus Fong <angus_fong_at_hotmail.com>
Date: 8 Jun 2003 20:47:42 -0700
Message-ID: <2db6dc80.0306081947.1116d2b9@posting.google.com>

hi,
I have a question on performace on insert statement.(see the end of message for performace), basically I ran a loop to insert 100,000 rows to a table, and the result is: NT machine wiht Raid 5 is significantly faster than Solaris box.
Can anyone tell me why? how can it make the Solaris box faster?

Windows NT 4.0, IBM netfinity, 4xP4 CPUs, 2G memeory, Raid5 only ~3300 rows/second

Solaris 5.8, 420R, 4 CPUs, 4G memory, RAID 0,1 (raw devices) ~2700 rows/second

Solaris 5.8, 6800, 6 processors board, 4G memory, RAID 0,1 (raw devices)
~2500 rows/second

---

Procedure TEST_TEST_TAB iS
v_total_rows number:=100000;
v_commit_rate number:=10000;
a varchar2(200) :='1234567890123456789012345'; v_id number :=0;
stime date;
etime date;
ttime number;

BEGIN

	DBMS_OUTPUT.Put_Line ('-->num rows '||v_total_rows);	
	DBMS_OUTPUT.Put_Line ('-->commit rate '||v_commit_rate);
	select sysdate into stime from dual;
	DBMS_OUTPUT.Put_Line ('-->start '||to_char(sysdate,'HH:MI:SS'));	

	for i in 1 .. v_total_rows loop
		insert into TEST_TAB
		(
		TEST_TAB_ID


		,COL11

		,COL12

		,COL13

		,COL14

		,COL15

		,COL16

		,COL17

		,COL18

		,COL19

		,COL20

		,COL21

		,COL22

		,COL23

		,COL24

		,COL25

		,COL26

		,COL27

		,COL28

		,COL29

		,COL30

		) values (
		TEST_TAB_ID.nextval --TEST_TAB_ID


		,a --COL11

		,a --COL12

		,a --COL13

		,a --COL14

		,a --COL15

		,a --COL16

		,a --COL17

		,a --COL18

		,a --COL19

		,a --COL20

		,a --COL21

		,a --COL22

		,a --COL23

		,a --COL24

		,a --COL25

		,a --COL26

		,a --COL27

		,a --COL28

		,a --COL29

		,a --COL30		

		);
		v_id :=v_id+1;
		if v_id >=v_commit_rate then
			commit;
			v_id :=0;
		end if;
		
	end loop;
	DBMS_OUTPUT.Put_Line ('-->end '||to_char(sysdate,'HH:MI:SS'));	
	select sysdate into etime from dual;
	ttime:=(etime-stime)*3600*24;
	DBMS_OUTPUT.Put_Line ('-->seconds '||ttime);	
	DBMS_OUTPUT.Put_Line ('-->rate '||v_total_rows/ttime);	

END; Received on Sun Jun 08 2003 - 22:47:42 CDT