IOT, memory and transaction time

hi,

We have a table with 4 columns - col1 int, col2 int, col3 char(32), col4 char(32).

Previously there were 2 indexes
unique index1 on col3, col1
index2 on col2, col1

Reads always read all columns.

We have recently changed the way this table works. The columns are all the same but because of the way we put data in we have changed the indexes.

Now the table is an Index Ordered table with col2,col3 as PRIMARY KEY. There is an additional index on col3,col2.

Reads have improved quite a lot but there is a problem with the writes. Previously, when adding 40,000 records at a time, write times were pretty consistent no matter how big the table (this transaction used to actually involve 200 updates of the same table followed by 40,000 inserts). With the new indexes, they get progressively worse with every write plateauing at around 10 minutes!! (this new transaction involves updating a dummy table with one record and one column 200 times (using a where clause that references the other table), followed by 40,000 inserts). The update part of the transaction basically checks that no-one else has updated since you read.

Why have write times changed so dramatically? Why do they get progressively worse? If it was the update statement that references the other table with a where clause, then why have Read times improved (using pretty much the same clause)? So I figured it probably wasn't the Update statement. So, looking at the Insert, why has this become so different. The columns are the same, it is just that one is Index Ordered and one isn't. The 40000 inserts will all have a col2 value that is the same but is greater than any previous write of col2. The col3 values may not be in order when written back - so is it just a memory issue? If we allocate more memory to the Oracle instance will that solve it? Does the memory have to be allocated to a specific area or is the SGA sufficient?

Thanks
Phil Received on Tue Apr 17 2007 - 04:26:38 CDT