RE: Need Performance advice

From: Graeme Farmer <graeme.farmer_at_mincom.com>
Date: Tue, 22 Jun 2004 09:18:20 +1000
Message-ID: <DA5B390A2CBAA64AB2367C2EDA3210F60CA656CF@tqxbneclu03.root.tequinox.com>

Harvinder,

Try tracing just the session that is performing the transaction and not using STATSPACK as you are picking up irrelevant stats.

eg DBWR is waiting on db file parallel write and LGWR is waiting on log file parallel write, however there is no evidence of any session being impacted by these (ie no free buffer waits on DBWR or no log buffer space/log file sync waits on LGWR).

If you can perform a 10046 trace then re-post with more accurate data then someone may be able to provide some help; as it stands we may only be able to help tune something that isn't relevant to your problem.

Cheers,
Graeme.

-----Original Message-----

From: Harvinder Singh [mailto:Harvinder.Singh_at_MetraTech.com] Sent: Tuesday, 22 June 2004 8:26 AM
To: oracle-l_at_freelists.org
Subject: Need Performance advice

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

--

This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Limited unless expressly stated otherwise.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Mon Jun 21 2004 - 18:15:48 CDT