Re: how to improve impdp

From: rjamya <rjamya_at_gmail.com>
Date: Tue, 9 Jul 2013 10:25:12 -0400
Message-ID: <CAGurbTP-GSbN=FzRCy2aNt1vkReHy7WLu_xLcR5WakHceMeOkA_at_mail.gmail.com>



You have only one dmp file so parallel isn't much use here. I bet most of your time is going in creating indexes and enabling constraints. I'd expdp with
dumpfile=filename.%u.dmp parallel=5

and then use similar params for impdp. That will ONLY help your with table data import. Unless your tables have degree > 1 (in source db) constraints will be enabled in non-parallel mode. Unless your indexes have degree > 1 (in source db) they will be created by a single process, i.e. parallel will be used by 4 DW threads will be idling..

You might want to consider this if your source db schema doesn't change much (i have done this and it worked very well for me) , 1. expdp in parallel to multiple dumpfile and impdp excluding index/constraints (probably have to exclude stats as well). 2. impdp on dumpfiles extract one script for index, one for constraints and potentially one just for stats.
3. using sed, modify index script to create each index in parallel, then do a 'alter index' noparallel.
4. using sed modify constraints script to enable constraints with novalidate (if applicable), this should save you lot of time. 5. run index script using sqlplus then constraints script and then run stats script,

Raj

On Tue, Jul 9, 2013 at 10:14 AM, Hsieh, Joan <Joan.Hsieh_at_tufts.edu> wrote:

> Hi List,
> I'm trying to use data pump to export and import from the AIX database to
> the LINUX database. The application is peoplesoft Finacial. The expdp take
> around 4 hours, the impdp take 7 hours which is a big concern on the
> weekend production cutover time.
>
> The parameter I used on the impdp as below, do you have any suggestion how
> to improve the impdp time?
>
> Thanks,
>
> Joan
>
> userid= /
> directory=dmpdir
> dumpfile=FMPROD_expdp.1300_070213.dmp
> PARALLEL=5
> logfile=impdp_FINPROD1_i07032013114001.log
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 09 2013 - 16:25:12 CEST

Original text of this message