RE: Need Performance advice

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Tue, 22 Jun 2004 10:07:38 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C5187D9216@ex2003.metratech.com>

Graeme,

I ran the trace with 10046,8 for this session and following are the wait events using tkprof. Any advice will be highly appreciated. Also I modified the procedure and instead of using range scan to select 1000 rows, I am now using bulk fetch and forall construct that might result in PX wait events.

Following is the waits for insert statement:

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
db file sequential read 5041 0.50 59.86 log file sync 208 1.01 1.18 log buffer space 136 0.00 0.14

Following is the total wait event including select from 1st table using bulk fetch:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 347 0.18 0.15 0 21 0 0
Execute 2480 38.68 101.69 5042 117354 1744837 1000025
Fetch 1908 12.28 15.47 5 1773 1 1001274
------- ------ -------- ---------- ---------- ---------- ----------

total 4735 51.15 117.32 5047 119148 1744838 2001299

Misses in library cache during parse: 33 Misses in library cache during execute: 29 Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
db file sequential read 5047 0.50 59.91 process startup 2 0.00 0.00 PX Deq: Join ACK 3 0.00 0.00 PX Deq: Parse Reply 1 0.00 0.00 PX Deq: Execute Reply 4358 0.25 1.04 log file sync 208 1.01 1.18 log buffer space 136 0.00 0.14 latch free 11 0.00 0.00 PX Deq: Signal ACK 3 0.00 0.00

8 user SQL statements in session.
341 internal SQL statements in session. 349 SQL statements in session.

Thanks
--Harvinder

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Graeme Farmer Sent: Monday, June 21, 2004 7:18 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Need Performance advice

Harvinder,

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

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,=3D20
=3D20
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.=3D20
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

=3D20

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

--=20

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 Tue Jun 22 2004 - 09:04:34 CDT