DSXchange

I am running a update query in oracle stage. The query is something like this
update A
set b=orchestrate.c,
d=orchestrate.e
This is supposed to update aroung one miilion record. The table A has around 10m records.
Now the job is configure to run in four parallel nodes. When it is running 4 nodes it went on for 2 days without updating a single row. When I checked the orcale database table A is locked by 4 processes, which I was expecting. Now when I recofigure the .apt file to run the job in 2 nodes it got finished in 3 mins. Is it because the four processes are locking the table simultaneously, it was not updating. If this is true how it has worked with 2 processes. I am bit confused. Can someone explain how exactly update statement works in parallel job.

So... no where clause?

Sorry..yes there was a where clause like this where x=orchestrate.z

What partitioning are you using? Would level of locks is table A using (table, page or row)?

we are not using any specific patitioning..It is Auto...

And regarding table locking I have not set any specific locking mechanism..as I do not know how to do it

Are there any duplicate in the key values ?

In the caluse 'where x=orchestrate.z'. So here both X and Z are primary key.

harshada wrote:In the caluse 'where x=orchestrate.z'. So here both X and Z are primary key.

What is that suppose to mean ?

Sorry..there are no duplicate in the key values...

Have you got the SQL associated with all those 4 blocked/blocking sessions from Oracle trace file?

would you mind to post it?

I have already posted how the update statement looks like...I can't post the exact update statement...

check in the oracle for any table locks, wait etc... check if your SQL is waiting for any lock to be released

How big is the table ?

Check whether you land up having table level locks due to the volume.

yes..the table is waiting for locks to be released..but these locks are created due to paralle processing..So the four parallel processing are in a dead lock state I believe..hence no rows are getting updated even if the job is running for days...the table contains more than 50m records...Now I want to undestand is there any mechanism bu which I can avoid this situation..