RE: Performance question
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 10:54:11 -0400
If you move one of the unique indexes (the pk or the unique index) to a
different tablespace,
does about half of the read wait move?
If so, and if you have a place to put the file that has an independent i/o
chain from the file for the other tablespace, you might be able to get them
to run in parallel (which assumes that batch is king and you don't have the
multi-user conundrum; still even against many users you may thus separate a
chronically busy i/o signature). Don't do any of this if your service times
look like memory accesses. Reporting your service times would be useful, by
the way.
Second, what can cause this? Well, a lotta stuff.
My hipshot guess from the available data is that you go from 30K tps to 18K
tps when the indexes gain a layer. That's about the right ratio.
Third, what is your commit pattern? Your report that the read time is piling
up on the file containing the indexes runs counter to this being a problem,
unless you stashed undo in the same tablespace.
Do I correctly understand that the 1000 row table is static once it is
created and you committed it?
good luck
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Harvinder Singh
Sent: Wednesday, June 23, 2004 10:24 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
