Update performance issue

algfr · Post by **algfr** » Tue Sep 08, 2009 8:31 am

There are indexes

I noticed this lock when I browed the SGA trace :

TX - row lock contention

happens when i have more that just a few rows to update.

Does this ring a bell ?

algfr · Post by **algfr** » Tue Sep 08, 2009 10:19 am

Also when there are many records, it creates 2 Oracle Session that are blocking each other...

Also tried that in the sequential mode, it's weird...

algfr · Post by **algfr** » Wed Sep 09, 2009 2:15 am

Do you know which conditions can cause two nodes to lock each other ?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Sep 09, 2009 2:33 am

Database level integrity constraints and commit intervals.

algfr · Post by **algfr** » Wed Sep 09, 2009 3:09 am

Sainath.Srinivasan wrote:Database level integrity constraints and commit intervals.

How is that ? Do you mean there is a configuration to be done ? Please explain.

algfr · Post by **algfr** » Tue Sep 15, 2009 2:23 am

algfr wrote:
Sainath.Srinivasan wrote:Database level integrity constraints and commit intervals.
How is that ? Do you mean there is a configuration to be done ? Please explain.

There seems to be both updates and inserts in the same time, thus causing locks. When the data volume is lower or the initial table is empty, it runs fine.

algfr · Post by **algfr** » Tue Sep 15, 2009 3:01 am

Also this happens only with Type 2 !

Seems the Inert and Update statement that takes place are blocking each other

priyadarshikunal · Post by **priyadarshikunal** » Tue Sep 15, 2009 3:18 am

whats being used as upsert mode,
insert then update or update then insert?

what happens if you lower the array size/transaction size/commit interval.

Oracle places a row level lock during insert/update. seems like update query is waiting for the inserted record to commit and then its updating the same.

thats what I told earlier to put enough constraint in update query to leave the inserts and update only old records. (unless you have duplicates on key which you are processing intentionally.)

algfr · Post by **algfr** » Tue Sep 15, 2009 5:06 am

priyadarshikunal wrote:whats being used as upsert mode,
insert then update or update then insert?

what happens if you lower the array size/transaction size/commit interval.

Oracle places a row level lock during insert/update. seems like update query is waiting for the inserted record to commit and then its updating the same.

thats what I told earlier to put enough constraint in update query to leave the inserts and update only old records. (unless you have duplicates on key which you are processing intentionally.)

Hi,

whats being used as upsert mode
insert then update or update then insert?

This is insert and update. This is the default mode when using a SCD stage.

what happens if you lower the array size/transaction size/commit interval.
Takes forever too

Oracle places a row level lock during insert/update. seems like update query is waiting for the inserted record to commit and then its updating the same.

I agree but the primary key should be different as it is a surrogate key handled by the SCD stage. A state file contains all keys.

thats what I told earlier to put enough constraint in update query to leave the inserts and update only old records. (unless you have duplicates on key which you are processing intentionally.)

Good idea but I don't know how to do that with a SCD stage.

priyadarshikunal · Post by **priyadarshikunal** » Tue Sep 15, 2009 5:41 am

This is insert and update. This is the default mode when using a SCD stage.

you can still change it to update then insert.

if its auto generated sql then it will surely put surrogate key in constraint to avoid any row level locks.

Monitor the space in rollback segment when the job is running.

row lock contention happens when

when a session is waiting for a row level lock that is already held by another session.

Frequent commits can resolve it.

if a session is waiting due to potential duplicates in UNIQUE index.

this won't take forever

if the session is waiting due to shared bitmap index fragment.

get rid of bitmap indexes. before load as it contains range of rowids which gets manipulated during insert.

you can also try to run it on one node for point no 2 and see what happens.

algfr · Post by **algfr** » Fri Jan 22, 2010 4:31 am

After weeks of investigation we found that server jobs were way faster because they were using OCI stage.

This issue comes out of the fact that it is not possible to specify the array size for update as it was for OCI with server.

In Server, when i try to put an array size of 1 for update, the performance becomes very slow as well.

Here is IBM answer :

"After lengthy discussion with engineering and change analysis for putting in new functionality required to get array processing in the Oracle EE stage, we agreed this should be an enhancement request to get the functionality added to future releases. You can always use the Oracle OCI stage on the parallel canvas to get the array processing feature for any job requiring Oracle updates. There is also the option of using the Oracle Connector already in version 8.1, when you decide to upgrade to that version. At this time, these are the options available to you to get the functionality you request."

priyadarshikunal · Post by **priyadarshikunal** » Fri Jan 22, 2010 6:06 am

Updates are way faster if there is no row contention. In case of parallel multiple are fired at once thus causing the problem. Running on one node with upsert mode update then insert solves that problem sometimes. In other case I prefer to split updates from inserts.

Adding functionality in future releases is a very vague answer and you never know when it will be added. So its better to go for workaround.

Never worked on 8.1 so can't comment on performance of Oracle Connector.