Need Performance advice

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Mon, 21 Jun 2004 18:25:48 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C518C2C7F7@ex2003.metratech.com>

Hi,=20
=20

We are testing an application prototype and inserting 1000 rows from 1 table and populating into 2nd table in a loop and inserting 1M rows.=20 2nd table have primary key on 2 column, 1 column unique key and 2 non-unique indexes each having 6 columns.

We were seeing tps of only about 5000 and after changing the reading table as IOT now it went up to between 6000-12000. I have also made the following changes since the first test:

Increase the SGA to 1GB from 170 Mb(db block buffers change from 25MB to 950MB) (Total RAM on machine 2GB)
Change the log file size to 1 GB each (3 log groups) and put on separate drive
Change the 1st table to IOT
Increase the Rollback and tempdb datafile to 10GB
Tables and indexes on separate tablespaces (I doubt if this will help as all are on same raid 5 disk sets )
using auto segment for tables and tried all auto, uniform extents for indexes

Now we are seeing following waits:
Top 5 Timed Events

% Total

Event                                               Waits    Time (s)

Call Time

-------------------------------------------- ------------ -----------
---------
db file parallel write                              8,215         103
28.70
db file sequential read                             1,877          96
26.72
CPU time                                                           61
16.84
class slave wait                                       10          51
14.19
log file parallel write                            12,697          22

6.22

Also it is worth noting that maximum of the read and write times are on 2 6 column non-clustered indexes. Why it is spending so much time on read for non-clustered indexes?
How can we reduce the waiting for db file parallel write and db file sequential reads?
Let me know what else I can try to improve the inserts 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
-----------------------------------------------------------------

Received on Mon Jun 21 2004 - 17:22:50 CDT