Update query hanging while running in parallel
Moderators: chulett, rschirm, roy
Update query hanging while running in parallel
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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..