Home -> Mailing Lists -> Oracle-L -> RE: Big Update/DML

RE: Big Update/DML

From: Reen, Elizabeth <"Reen,>
Date: Wed, 26 Aug 2020 14:30:31 +0000
Message-ID: <643f6e3a7172480c81f6bc0839badf7b_at_imcnam.ssmb.com> 








Be careful with how you do parallelism.  Done correctly it will speed things up.  Done incorrectly and you will have a locking nightmare.  Are you updating the columns with the same value?  If so, the default value option might be very useful.



Liz



From: [freelists.org] oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of [freelists.org] Sanjay Mishra
Sent: Tuesday, August 25, 2020 11:29 PM
To: oracle-l_at_freelists.org


Subject: Big Update/DML



Hi Everyone



I am working on 19c and need to do one time update on multiple tables containing 3-4 Billions records and some tables are Compressed for OLTP and some are uncompressed. Tables have multiple columns but updating only one new column added with data from another column from the same table. Environment is on Exadata with Buffer Cache of 60G and CPU_count of 30



Update using high Parallel DMl enabled are taking several hours to even a day per table and are using high UNDO




    
  
  1.   Does dropping index even the column updated has no relation to Indexed column can help the Elapsed time
  
  2.   Does Compress table will help in this scenario vs uncompressed Table. Table size with compress for OLTP is around 800G and same kind of another table is 4 Tb without compression. Trying to see that if compression can help in using less IO or buffer cache from both Table and Index perspective
  
  3.   Does adding more SGA or CPU can help in allocating more Parallel threads to reduce the Elapsed time






I was checking and found that dbms_parallel_execute can be good solution. Can someone update if they had used for Big Update and can share his sample code to try



TIA



Sanjay


--
http://www.freelists.org/webpage/oracle-l


Received on Wed Aug 26 2020 - 16:30:31 CEST












Original text of this message