Update & Insert Concurrency

[Dagon]

Running 9.2.0.4.0 - 64bit on solaris, I have a number of large insert jobs. In parallel with these, I am trying to run some updates against the same table. The rows being updated have a different feed_id to those being inserted, so there is no overlap between them.

What I've noticed is that insert speed doesn't seem to be much affected by running updates, but the update speed falls fairly dramatically while the inserts are running. I can do the update of 10000 rows in about 40 seconds on its own but with an insert running it can take 4 minutes. I would guess that this is down to some sort of locking contention rather than just machine contention.

I was wondering if there are any parameter settings or such like that I could tweak to improve the performance of this set up. More radical options like partitioning the table into different feed_ids are not feasible.

 

[sem]

You may consider inserting in append mode. BTW do you have enough space for udates to fit the same blocks?

Regards, Dima

http://seminihin.narod.ru

 

[Dagon]

I was only updating one 7 character column, so I doubt whether it went pass the pctfree values for the block. In any case, that would have shown up as poor performance on the update without the insert running.

I don't think I can use append mode because the inserts are being done through an Informatica job.