RE: "Preparing & Executing Massive DML Operations e.g. updates."
- From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- To: "fmhabash@xxxxxxxxx" <fmhabash@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Aug 2013 20:59:21 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of fmh
Sent: Monday, August 19, 2013 8:18 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
