Update query hanging while running in parallel

harshada · Post by **harshada** » Fri Dec 11, 2009 11:07 am

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.

chulett · Post by **chulett** » Fri Dec 11, 2009 11:16 am

So... no where clause?

harshada · Post by **harshada** » Fri Dec 11, 2009 11:48 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Dec 11, 2009 2:57 pm

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

harshada · Post by **harshada** » Mon Dec 14, 2009 10:21 am

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

harshada · Post by **harshada** » Mon Dec 14, 2009 10:22 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Dec 14, 2009 10:34 am

Are there any duplicate in the key values ?

harshada · Post by **harshada** » Mon Dec 14, 2009 11:29 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Dec 15, 2009 3:43 am

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

What is that suppose to mean ?

harshada · Post by **harshada** » Tue Dec 15, 2009 3:53 am

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

priyadarshikunal · Post by **priyadarshikunal** » Tue Dec 15, 2009 5:16 am

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

would you mind to post it?

harshada · Post by **harshada** » Tue Dec 15, 2009 12:37 pm

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

keshav0307 · Post by **keshav0307** » Tue Dec 15, 2009 6:57 pm

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Dec 16, 2009 3:24 am

How big is the table ?

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

harshada · Post by **harshada** » Wed Dec 16, 2009 8:16 am

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..