| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Performance question
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 TotalWaited
Thanks
--Harvinder
 |
 |