RE: impdp performance in 11.1 vs 11.2

I will have to check to see which parameters changed, if it is the result of a parameter change then it would need to be something that gets set by default since I am doing an in place upgrade.

I think I might have found the issue though, but have not confirmed. I am rerunning the 11.1.0.7 impdp this morning, and it looks like the index creations are not running in parallel. I have 1 of the 4 processes doing work and the other 3 are sitting on "wait for unread message on broadcast channel". All 4 processes were loading data during the table creations. I will upgrade again after this to see if 11.2.0.4 runs the index creation in parallel

From: Andy Wattenhofer [mailto:watt0012_at_umn.edu] Sent: Wednesday, August 20, 2014 9:40 AM To: Michael Schmitt
Cc: ORACLE-L
Subject: Re: impdp performance in 11.1 vs 11.2

Are there any parameters set differently between the two? Specifically be on the lookout for deferred_segment_creation, as I believe that was added in 11.2 and the default is TRUE. That would make the 11.2 import take less time because it is not creating segments for empty tables.

Andy

On Tue, Aug 19, 2014 at 9:50 PM, Michael Schmitt <mschmitt_at_uchicago.edu<mailto:mschmitt_at_uchicago.edu>> wrote: Hello,

I was wondering if someone might know off the top of their head why I am seeing such a dramatic difference in runtimes for impdp tests between 11.1.0.7 and 11.2.0.4 versions

I run the following for impdp.
impdp directory=migrate_dir SCHEMAS=X,Y,Z,A,B,C,D dumpfile=exp_TSTR_%U.dmp PARALLEL=4

The above impdp takes about 3 hours when the database is 11.1.0.7. I upgrade the database to 11.2.0.4 with dbua and the import takes about 1hr10mins. No other changes made besides the upgrade, unless dbua changed something that I am not aware of right away. I restored the database back to 11.1.0.7 just to confirm it takes 3 hours.

I haven't really checked what is going on in the database during the import since I don't have all my access in place yet. Was wondering if someone might know of the top of their head. Seems like the 11.1.0.7 impdp gets held down during the index builds

Thanks,
Mike

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