(update): speeding up expdp

From: Maureen English <maureen.english_at_alaska.edu>
Date: Mon, 07 May 2012 14:23:51 -0800
Message-ID: <4FA84B77.9010804_at_alaska.edu>

Thank you all for your suggestions, especially the ones with information on how to find out where the bottleneck might be!

The sysadmin is looking at options for the NFS mount. Other than that, it looks like the the 2 large tables with LOB columns are my problem. I did try using exp instead of expdp and was able to reduce the export time by a third.

There is some good news, though. The system I've been working on is a development system with 4 databases running on it. The production systems that I'll be doing the final export from are faster machines with more cpus and more memory and only one database on each system. I'm hoping to get access to the production systems soon in order to test the export of the 30G table and see how it compares to the dev system.

  • Maureen

D'Hooge Freek wrote:
> Maureen,
> Is your database an enterprise edition or a standard edition?
> If you have an enterprise edition, then expdp can work with multiple parallel slaves.
> One thing you then need to look for is if the 2 large tables are also exported in parallel or if only 1 process is exporting them.
> If the table for instance has a lob column, then oracle can't export it in parallel.
> Sometimes Oracle chooses for some reason wrongfully the direct path method for a table, which disables the parallel feature. In that case you can try to force expdp to use the external table method.
> The notes below give you a good starting point on how to improve the performance of datapump and how to check what is happening:
> Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp) [ID 453895.1]
> Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1]
> Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ? [ID 552424.1]
> Kind regards,
> Freek D'Hooge
> Uptime
> Oracle Database Administrator
> email: freek.dhooge_at_uptime.be
> tel +32(0)3 451 23 82
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Maureen English
> Sent: zaterdag 5 mei 2012 3:58
> To: oracle-l_at_freelists.org
> Subject: speeding up expdp
> Hi,
> We have an Oracle 10g database on RHEL5. I would love to find a way
> to speed up the export
> of the database. It currently takes about 7.5 hours. The main
> problem is with 2 tables, one of
> which is 30GB, the other is 70GB.
> I've looked through the documentation for expdp, as well as gone
> through all of the Metalink notes
> I could find that were related to expdp running slowly. I've tweaked
> things here and there, but I
> can't get the export to run any faster. I know the system is a little
> slow, and I'm writing to an
> NFS mounted drive, but I was really hoping that this could be done
> faster. I haven't tried using
> exp instead, but I'm considering that for another test.
> Any suggestions?
> - Maureen
> --
> http://www.freelists.org/webpage/oracle-l

Received on Mon May 07 2012 - 17:23:51 CDT

Original text of this message