RE: how to improve impdp

From: Hsieh, Joan <Joan.Hsieh_at_tufts.edu>
Date: Tue, 9 Jul 2013 18:31:50 +0000
Message-ID: <416FB269D9362D4AA40BE7124CA3EF9F0AB92F19_at_SSVMEXDAG01MB02.tufts.ad.tufts.edu>



Hi David,

The database is dataguard environment, so it has to be in archive mode. In cutover time windows, I don't want to break the standby database and have to recreate it later. Our cutover time is combined the AIX database move to LINUX and one peopletools upgrade. So there are two projects going on in the same open window. Peoplesoft tech team it is ok just import data only without the statistics and indexes since both can be taken care in the peoplesoft upgrade. I'm trying to do another test without statistics and indexes.

"Drop user sysadm cascase" on the new LINUX server take almost the whole day to finish. That's another issue.

Joan

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Kurtz Sent: Tuesday, July 09, 2013 11:47 AM
To: 'oracle_l'
Subject: RE: how to improve impdp

When you run the import in parallel what is your bottleneck? What does OEM or the AWR Report show you?

  • if you are waiting on redo or logfile sync increase parallelism probably isn't going to help. I suppose you could run the database in noarchive log mode during the import because you have very little to lose. However, if this is the case during an import you are probably going to have performance problems with the Financials batch processing when you go live!
  • Quite a lot of this thread has discussed parallelising the export/import. If you are not simply waiting on disk that might work. Aside from unique indexes, there is no database enforced referential integrity in PeopleSoft. So, you could easily separate the tables into different exports and import them concurrently too. Everything in PeopleSoft is in on schema (by default SYSADM) and one other object is in the PS schema. Afterwards you can use the PeopleSoft DDDAUDIT report to make sure all the tables and views are present. You could also use PeopleTools App Designer to double check that there are no discrepancies between the PeopleTools data dictionary and the reality of the database.

Rebuilding your indexes could also be time consuming. PeopleSoft usually creates them parallel and then alters them noparallel. So the create commands during the Import may not run in parallel (to be honest I haven't checked). You could just use PeopleTools App Designer to build an index build script that will build all the indexes in parallel and alter them noparallel again.

regards



David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iotzov, Iordan
Sent: 09 July 2013 16:01
To: Joan.Hsieh_at_tufts.edu; oracle_l
Subject: RE: how to improve impdp
Importance: High

If impdp over DB link approach is not feasible and IO throughput to/from the export/import files is not an issue, you might try "overlapping" the export and the import. You can split the 4 hour export into a number of smaller exports. Run the first small export and upon completion concurrently start the corresponding small import and the next small export. After the next small export is done, start the next small export and run (or queue) the corresponding small import, and so on.

To be done safely, this approach requires knowledge of schemas, tables and constraints in the underlying database. Thorough testing is a must - make sure that ALL objects are accounted for.

Iordan Iotzov
http://iiotzov.wordpress.com/

Check out SmartSource Xpress, our new iPad app! Follow us on Twitter | Like us on Facebook

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hsieh, Joan
Sent: Tuesday, July 09, 2013 10:15 AM
To: oracle_l
Subject: how to improve impdp

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

This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.

--

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 09 2013 - 20:31:50 CEST

Original text of this message