Possible things to look:
- Set your table1's index (or indexes) to nologging mode to reduce redo
writing.
- If the table1 data does not get "updated" after insert, then set "PCTFREE
0" for table1 might help.
Guang
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Harvinder Singh
Sent: Tuesday, July 27, 2004 3:46 PM
To: oracle-l_at_freelists.org
Subject: Insert Performance question
Hi,
We are having insert performance problem and need some suggestions on
tuning.=20
Overview of the scenario
In our application, one of the main components is insertion of about 2
million records into big usage table by selecting 1000 rows in a batch
from 2nd table. In real scenario second table will be truncated after
every 1000 records but we are simulating by storing 1M records in 2nd
table and using bulk collect/forall selecting 1000 rows at a time and
insert into 1st table.
Also we are doing the same test on SQL Server. With 2 sessions inserting
1M each by selecting from same table and inserting into 1st table
inserts_rows_per_Second on SQL Server is 25000 whereas on Oracle is only
10000.
Tab1 is having about 80M rows, 1 primary key on 2 columns, 1 unique key
on varbinary column, 2 1 column indexes.
I checked with trace 10046, iostat, v$views and following are the
waitstats:
Oracle is spending most of the time reading from the index
tablespace(where all the 4 indexes are stored) and corresponding wait
event is "db file sequential read"
2nd time consuming wait event is "log file sync"
And then "read by other session" etc
Configuration
Oracle 10g on Red hat linux 9. 2 14 disk 500G each raid 0 array and 1
34G scsi hard drive
Boot,swap,OS on 34 G drive
Index tablespace,system,redo logs on 1 raid array
Tab1,tab2, rollback on 2nd raid array
Tab2 is IOT. Tablespace1 which stored data for tab1 and Index tablespace
both are having 5 10G files each with uniform extent size of 16M.
Database block size is 8k.
RAM on system is 2G and SGA size is 1.2G, data_buffer_cache having 1G
Log buffer size is 4M, 3 Log files each having 2G size.
Using AUTO UNDO and tabelspace size is 10G
Database is in NOARCHIVING mode.
2 cpu's and cpu usage is about 30-55% for both sessions.=20
What can be the possible options that we can try to speed up the insert
performance?
Thanks
--Harvinder
=20
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
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 - 14:52:02 CDT
