Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Insert performance problem

RE: Insert performance problem

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 17 Jun 2004 17:42:55 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C5187D9207@ex2003.metratech.com>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared.Still_at_radisys.com
Sent: Thursday, June 17, 2004 4:16 PM
To: oracle-l_at_freelists.org
Subject: Re: Insert performance problem  

Run a 10046 level 8 trace and see where the time is spent.

"Harvinder Singh" <Harvinder.Singh_at_MetraTech.com> Sent by: oracle-l-bounce_at_freelists.org
06/17/2004 12:23 PM
Please respond to
oracle-l_at_freelists.org
To
<oracle-l_at_freelists.org>
cc  

Subject
Insert 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_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 Thu Jun 17 2004 - 16:43:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US