Home » RDBMS Server » Server Utilities » data pump impdp is SLOOOOOOW
data pump impdp is SLOOOOOOW [message #463122] Tue, 29 June 2010 14:12 Go to next message
lawadm1
Messages: 3
Registered: June 2010
Junior Member
I'm hoping that someone can help me with this. I've tried using data pump (imdp) rather than imp to see if import time speeds up. I hear it's suppose to anyway.

It has taken almost 10 hours to import the data using data pump, and it usually takes 4 hours the old way with imp.

Below is the command that I'm using, and hopefully someone will see something that stands out where I have something incorrect. I thought using the CONTENT=DATA_ONLY would also help speed things up.

impdp lawdev/lawdev90 schemas=lawprd REMAP_TABLESPACE=LAWPRD:LAWDEV REMAP_SCHEMA=LAWPRD:LAWDEV DIRECTORY=IMPORT DUMPFILE=gmkprd2_exp%U.dmp CONTENT=DATA_ONLY EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX TABLE_EXISTS_ACTION=TRUNCATE logfile=LAWDEV_impdp.log

Thanks,
Jeff
Re: data pump impdp is SLOOOOOOW [message #463125 is a reply to message #463122] Tue, 29 June 2010 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Command line looks reasonable to me.
Re: data pump impdp is SLOOOOOOW [message #463131 is a reply to message #463125] Tue, 29 June 2010 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use PARALLEL clause if your hardware, OS workload and configurationallow it.
You have to know that TRUNCATE actions can take long (above all if you have a large buffer cache) and this is not include in former imp so you cannot directly compare.

Regards
Michel

[Updated on: Tue, 29 June 2010 15:07]

Report message to a moderator

Re: data pump impdp is SLOOOOOOW [message #463137 is a reply to message #463131] Tue, 29 June 2010 15:10 Go to previous messageGo to next message
lawadm1
Messages: 3
Registered: June 2010
Junior Member
Do you have an example of how I would use that clause? We're on an HP Integrity server running 11iv3, and Oracle 10gr2 (64 bit). This is a test system, so the OS workload is minimal, and usually things run pretty darn fast on this server.
Re: data pump impdp is SLOOOOOOW [message #463138 is a reply to message #463137] Tue, 29 June 2010 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PARALLEL just gives the number of parallel slaves that work on your import, so you can use it as far as you have enough io bandwith and cpu power, just try it as this is test system.

Regards
Michel

[Updated on: Tue, 29 June 2010 15:13]

Report message to a moderator

Re: data pump impdp is SLOOOOOOW [message #463140 is a reply to message #463138] Tue, 29 June 2010 15:23 Go to previous messageGo to next message
lawadm1
Messages: 3
Registered: June 2010
Junior Member
Once I mentioned this to the DBA, he said that he forgot about the PARALLEL parameter. Then he said that we actually use that parameter (PARALLEL=4) on the export, which is probably why we have 4 different dump files. And then here I am using the default of 1 on the import. I'll try this on the next test refresh of our production data, and hopefully we'll see a significant difference.

Thanks for the help.

Jeff
Re: data pump impdp is SLOOOOOOW [message #463664 is a reply to message #463122] Fri, 02 July 2010 08:42 Go to previous messageGo to next message
ejdrba
Messages: 27
Registered: May 2005
Location: Kolkata
Junior Member

Hi, try using COMMIT=Y and DIRECT=Y
Re: data pump impdp is SLOOOOOOW [message #463705 is a reply to message #463664] Fri, 02 July 2010 11:56 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello Ejdrba;

Are you sure COMMIT & DIRECT will resolve this issue? Please read entire thread you may understand lawadm1 question.

Babu
Previous Topic: SQL Loader
Next Topic: please Help....5-1 Performing a Simple Schema Export Errors.
Goto Forum:
  


Current Time: Thu Mar 28 05:29:46 CDT 2024