Performance question
- From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 10:23:53 -0400
Hi,
Scenario overview
------------------
We are testing an application prototype and inserting 1000 rows at a
time 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
2 non-unique indexes each having 6 columns. This is 10g on WIN2K.
I created the tablespaces with uniform extent size and allocate
sufficient minextents to table and all indexes so that no need to
allocate more extent.
Now when I start inserting for first 2 runs transaction per secons(tps)
inserted are around 25000-30000 but suddenly on 3rd run I see tps drop
to 18000 and then sometimes 30000 and other times 18000. I didn't see
any new allocation for tables and indexes, no log switches since the
file is large enough, no increase in temp tablepspace increase, some
waits but that are for both when tps was 30000 and 18000. What can be
the possible reason for such difference in tps numbers with running same
script?
Wait events looks like: . 98% of db file sequential read wait is on file
containing indexes for table.
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
Thanks
--Harvinder
----------------------------------------------------------------
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:
