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

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Mon, 19 Aug 2013 10:48:15 -0500
Message-ID: <CAJvnOJZZq=ay53BHpnapdJCQ7kqzM5YantAom_deujR6a-Z7Hg_at_mail.gmail.com>

Typically in a production instance undergoing constant load, I would never turn off archive logging. The risks are just not worth it. Are you doing a merge statement or an update statement?

On Mon, Aug 19, 2013 at 10:37 AM, Tim Gorman <tim_at_evdbt.com> wrote:

> 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.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


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

Received on Mon Aug 19 2013 - 17:48:15 CEST