Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance question

RE: Performance question

From: Mark W. Farnham <>
Date: Wed, 23 Jun 2004 10:54:11 -0400
Message-ID: <>

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


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

[]On Behalf Of Harvinder Singh Sent: Wednesday, June 23, 2004 10:24 AM
Subject: Performance question


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


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Wed Jun 23 2004 - 09:57:29 CDT

Original text of this message