Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_PARALLEL_EXECUTE help needed. (Oracle 11.2g)
icon5.gif  DBMS_PARALLEL_EXECUTE help needed. [message #573370] Thu, 27 December 2012 06:41 Go to next message
napster
Messages: 8
Registered: December 2012
Junior Member
I want to update 10 production tables for 8 to 10 columns with some default value.

All tables are having approximately 60 crore data and we can not take downtime for this update process.

All tables are having objid column as primary key of varchar2 datatype.

I want to use DBMS_PARALLEL_EXECUTE for this process but i have some other requirement also.

I want to update the records as batch wise. For example i want to update the table for 1,00,000 rows for one task. I want to know how to create chunks for 1,00,000 rows. Please explain with example.
I want to know which chunk method is best for this process (CHUNK_BY_ROWID or CHUNK_BY_SQL).
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573372 is a reply to message #573370] Thu, 27 December 2012 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 60000
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE_CHUNKS_BY_ROWID (...BY_ROW=>TRUE, CHUNK_SIZE=100000)

Regards$
Michel
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573375 is a reply to message #573370] Thu, 27 December 2012 09:06 Go to previous messageGo to next message
napster
Messages: 8
Registered: December 2012
Junior Member
Thanks Michel.

Is there any way to know that how many records are processed by one chunk ?

Re: DBMS_PARALLEL_EXECUTE help needed. [message #573379 is a reply to message #573375] Thu, 27 December 2012 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 60000
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBA_PARALLEL_EXECUTE_CHUNKS

Not really directly how many rows but you have the start and end rowids for each chunk, so you can yourself query the table to know how many rows between start and end.

Regards
Michel
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573429 is a reply to message #573370] Fri, 28 December 2012 06:30 Go to previous messageGo to next message
napster
Messages: 8
Registered: December 2012
Junior Member
What happen if some update is going on the table and if at the same time i execute the parallel task ?

Are there any chances of Deadlock occurance?
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573435 is a reply to message #573429] Fri, 28 December 2012 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60000
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What happen if some update is going on the table and if at the same time i execute the parallel task ?


The same thing as usual, one is blocking the other one.

Quote:
Are there any chances of Deadlock occurance?


Yes there are.

Regards
Michel
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573479 is a reply to message #573370] Sat, 29 December 2012 07:21 Go to previous messageGo to next message
napster
Messages: 8
Registered: December 2012
Junior Member
Thank's Michel.

Can you please tell me any other way to update such a huge live transactional data ?

Re: DBMS_PARALLEL_EXECUTE help needed. [message #573483 is a reply to message #573479] Sat, 29 December 2012 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60000
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A single big UPDATE?

Regards
Michel
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573824 is a reply to message #573370] Thu, 03 January 2013 06:48 Go to previous messageGo to next message
napster
Messages: 8
Registered: December 2012
Junior Member
There are such 9 tables to update and these tables are master tables. Each table is having near about 60 crore data. These are highly queried tables...
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573831 is a reply to message #573824] Thu, 03 January 2013 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds like you really need some downtime
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573836 is a reply to message #573831] Thu, 03 January 2013 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 23152
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: DBMS_PARALLEL_EXECUTE help needed. [message #573840 is a reply to message #573824] Thu, 03 January 2013 08:43 Go to previous message
Michel Cadot
Messages: 60000
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The more chunks you define, the less likelyhood you will have deadlocks but you cannot remove all probability to have these deadlocks.

Note that if the other sessions are OLTP then they update only one row at a time and so you can't have deadlock with them.

Otherwise, you have to either accept the deadlocks, either accept application downtime.

Regards
Michel
Previous Topic: Procedure changes
Next Topic: execute immediate vs ref cursor
Goto Forum:
  


Current Time: Sat Dec 20 09:37:10 CST 2014

Total time taken to generate the page: 0.05166 seconds