Re: "Preparing & Executing Massive DML Operations e.g. updates."
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 19 Aug 2013 09:37:01 -0600
How about not running MERGE, UPDATE, or DELETE commands at all, and
instead converting them into faster and more scalable INSERT /*+ APPEND
PARALLEL */ commands, using table renames or partition-management
commands (such as EXCHANGE PARTITION) to "publish" new data from old?
http://evdbt.com/videos/
Hope this helps...
Thanks!
On 8/19/2013 9:18 AM, fmh wrote:
> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
