RE: Insert performance problem
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jun 2004 19:03:23 -0400
Using the append hint will speed up the process, but you can't see neither
do another insert until you do a commit.
-------Original Message-------
From: oracle-l@xxxxxxxxxxxxx
Date: 06/17/04 17:50:19
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Insert performance problem
Following is the trace and it is doing sequential read on both table from
which to read and to write.
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited Waited
db file sequential read 13910 0.46 71.20
undo segment extension 3260 0.00 0.00
buffer busy waits 147 0.00 0.00
log file sync 43 0.00 0.06
log buffer space 13 0.00 0.00
log file switch completion 2 0.07 0.13
If I modify the query and try to insert 1M records at same time instead of
1000, trace changes like this:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited Waited
db file sequential read 804 0.47 11.79
db file scattered read 885 0.55 37.44
undo segment extension 425 0.00 0.00
buffer busy waits 30 0.00 0.00
log buffer space 54 0.53 0.64
log file switch completion 2 0.06 0.10
log file sync 3 0.00 0.00
latch: cache buffers lru chain 1 0.00 0.00
Thanks
--Harvinder
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jared.Still@xxxxxxxxxxx
Sent: Thursday, June 17, 2004 4:16 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Insert performance problem
Run a 10046 level 8 trace and see where the time is spent.
"Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
06/17/2004 12:23 PM
Please respond to
oracle-l@xxxxxxxxxxxxx
To<oracle-l@xxxxxxxxxxxxx>
cc
SubjectInsert performance problem
Hi,
We are testing a application prototype and inserting 1000 rows from 1 table
and populating into 2nd table in a loop and inserting 1M rows.
2nd table have primary key on 2 column, 1 column unique key and 1 non-unique
index on 6 columns
We are seeing tps of only about 5000 whereas if I run same test on sql
server tps is between 15000 to 20000
Following are the stats on the machine:
Oracle 10g on win2k
Writing to hard disk having all the datafiles(raid 5) :Avg disk bytes/write
9000 and 13000
Writing to hard disk having log files :Avg disk bytes/write 48000 and 70000
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
