Re: Cloning 21 GB Table - Review Approach please

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 11 Mar 2005 08:36:42 -0000

I cam across a very odd "bug" recently with
a parallel insert append into a partitioned table,
9.2.0.5 if I recall correctly.

The co-coordinator spawned two sets of PX
slaves to do a parallel to parallel distribution
between the select and insert - and then
dumped the pipeline to the temp tablespace.

Apart from crashing with "unable to allocate
in TEMP" on the first attempt, it also took
about 4 times as long as it should have done
to complete.


/*+  nologging */  -- I haven't seen that as
a hint, and it doesn't seem to do anything on
my 9.2.0.6 system.

In fact, a quick test showed a rather counter
intuitive results:

insert /*+ append nologging */
select rownum , rpad('x',1000)
into all_objects

29,000 rows inserted


Redo size (v$mystat)

Append Nologging            34,456,000
Append                             34,456,000
no hint                                32,262,000

...
No indexes; database in archivelog mode,
table declared as logging to see if the nologging
hint worked.



Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






----- Original Message ----- 
From: "Tim Gorman" <tim@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 10, 2005 10:57 PM
Subject: Re: Cloning 21 GB Table - Review Approach please


> Will Arhive Redo Log be generated by the INSERT /*+ APPEND */ Command if =
> the Target Table has been PRE-Created with NOLOGGING?

No.  Won't generate any "undo" either.

>
> Any possibility of giving Hint for NOLOGGING in the INSERT Command ?

Yes. /*+ APPEND NOLOGGING */

Why not use the built-in parallelism of Oracle?  Unless you've left
PARALLEL_MAX_SERVERS at the default of 5, you can add the PARALLEL hint to
both the INSERT and SELECT clauses.  You might also want to precede with
ALTER SESSION ENABLE PARALLEL DML and (of course) succeed with a COMMIT...

    alter session enable parallel dml;
    insert /*+ append nologging parallel(x, 4) */ into <target> x
    select /*+ full(y) parallel(y, 4) */ ... from <source> y;
    commit;

No faster way to clone.  Your mileage may vary, but if you have a decent
sized box it shouldn't take more than 60-90 mins, tops...

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


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

Other related posts: