RE: "Preparing & Executing Massive DML Operations e.g. updates."

From: Josh Collier <Josh.Collier_at_banfield.net>
Date: Mon, 19 Aug 2013 20:59:21 +0000
Message-ID: <D0534F8D31056242BE8E38FA9413FDA817E811C5_at_M1EXCHMB11.mmi.local>

Instead of updating the table create a new table and rename it, building indexes after the CTAS is done.

Alter session force parallel DML
Create table blerg parallel as select /*+ parallel(source) */ * from source Rename source source_old
Rename blerg source
Create indexes on source

Such an update with never finish.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of fmh Sent: Monday, August 19, 2013 8:18 AM
To: oracle-l_at_freelists.org
Subject: "Preparing & Executing Massive DML Operations e.g. updates."

I usually prepare for such ops ...
- Instance prep: disable archive logs, use no logging ,... etc.

• Session prep: enable PQ, PDML, ...etc
• SQL Prep: rewrite it e.g. updateble inline views vs. correlated queries.
• Use BULK SQL if need to. I'm running an update on a partitioned table of 44M rows. It has been 3 days now and there seems end is not near.
  1. The update session is the only FG session in the db. v$session_longops show the query involved but all rows have 'sofar=totalwork'. The view has not updated since 2 days ago. How else can progress be monitored?
  2. I did enable PDML ,but on 10.2.0.3, we hit this bug. The workaround did not work to disable the recycle bin. This was a major set back. Bug 4896424
• Parallel DML can fail with ORA-60 (Doc ID 4896424.8)

If you've had any experience with such operations, please provide your comments/feedback to the above inquiries.

-- 

----------------------------------------
Thank you


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


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

Received on Mon Aug 19 2013 - 22:59:21 CEST