Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Don't forget forall and bulk inserts. =0D
=0D
Juan Carlos Reyes Pacheco=0D
OCP=0D
-------Original Message-------=0D
=0D
From: oracle-l_at_freelists.org=0D
Date: 07/27/04 15:45:43=0D
To: oracle-l_at_freelists.org=0D
Subject: Insert Performance question=0D
=0D
Hi,=0D
=0D
We are having insert performance problem and need some suggestions on=0D
tuning.=3D20=0D
Overview of the scenario=0D
-------------------------=0D
In our application, one of the main components is insertion of about 2=0D
million records into big usage table by selecting 1000 rows in a batch=0D
from 2nd table. In real scenario second table will be truncated after=0D
every 1000 records but we are simulating by storing 1M records in 2nd=0D
table and using bulk collect/forall selecting 1000 rows at a time and=0D
insert into 1st table.=0D
Also we are doing the same test on SQL Server. With 2 sessions inserting=0D
1M each by selecting from same table and inserting into 1st table=0D
inserts_rows_per_Second on SQL Server is 25000 whereas on Oracle is only=0D
10000.=0D
Tab1 is having about 80M rows, 1 primary key on 2 columns, 1 unique key=0D
on varbinary column, 2 1 column indexes.=0D
I checked with trace 10046, iostat, v$views and following are the=0D
waitstats:=0D
Oracle is spending most of the time reading from the index=0D
tablespace(where all the 4 indexes are stored) and corresponding wait=0D
event is "db file sequential read"=0D
2nd time consuming wait event is "log file sync"=0D
And then "read by other session" etc=0D
=0D
Configuration=0D
--------------=0D
Oracle 10g on Red hat linux 9. 2 14 disk 500G each raid 0 array and 1=0D
34G scsi hard drive=0D
Boot,swap,OS on 34 G drive=0D
Index tablespace,system,redo logs on 1 raid array=0D
Tab1,tab2, rollback on 2nd raid array=0D
Tab2 is IOT. Tablespace1 which stored data for tab1 and Index tablespace=0D
both are having 5 10G files each with uniform extent size of 16M.=0D
Database block size is 8k.=0D
RAM on system is 2G and SGA size is 1.2G, data_buffer_cache having 1G=0D
Log buffer size is 4M, 3 Log files each having 2G size.=0D
Using AUTO UNDO and tabelspace size is 10G=0D
Database is in NOARCHIVING mode.=0D
2 cpu's and cpu usage is about 30-55% for both sessions.=3D20=0D
=0D
What can be the possible options that we can try to speed up the insert=0D
performance?=0D
=0D
Thanks=0D
--Harvinder=0D
=3D20=0D
=0D
=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ:
http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request_at_freelists.org=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at
http://www.freelists.org/archives/oracle-l/=0D
FAQ is at
http://www.freelists.org/help/fom-serve/cache/1.html=0D
- Binary/unsupported file stripped by Ecartis --
- Type: Image/jpeg
- File: 397220_backg_tile_new.jpg
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Jul 27 2004 - 15:48:09 CDT
