Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Insert Performance question

Re: Insert Performance question

From: Juan Carlos Reyes Pacheco <>
Date: Tue, 27 Jul 2004 16:45:21 -0400
Message-Id: <4106BEE1.000005.01536@CACHITOSS>

Content-Type: Text/Plain;
Content-Transfer-Encoding: quoted-printable Don't forget forall and bulk inserts. =0D

Juan Carlos Reyes Pacheco=0D
-------Original Message-------=0D

Date: 07/27/04 15:45:43=0D
Subject: Insert Performance question=0D


We are having insert performance problem and need some suggestions on=0D tuning.=3D20=0D
Overview of the scenario=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

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

What can be the possible options that we can try to speed up the insert=0D performance?=0D

Please see the official ORACLE-L FAQ:
To unsubscribe send email to: put 'unsubscribe' in the subject line.=0D --=0D
Archives are at FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jul 27 2004 - 15:48:09 CDT

Original text of this message