RE: speeding up expdp

  • From: Michael Dinh <mdinh@xxxxxxxxx>
  • To: "'maureen.english@xxxxxxxxxx'" <maureen.english@xxxxxxxxxx>, D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
  • Date: Sat, 5 May 2012 17:41:56 -0700

I feel sorry for you, but misery loves company :=)

4GB database full backup using RMAN takes over 6 hours to NFS. 

Off the tangent now.

dumpfile=migration_wh%U.dmp <== important since this was the mistake I made 

Hopefully, it's that easy. Otherwise, there are several articles on configuring 
NFS for use with Oracle.

Michael Dinh
Disparity Breaks Automation (DBA)

Confidence comes not from always being right but from not fearing to be wrong - 
Peter T Mcintyre
Great minds discuss ideas; average minds discuss events; small minds discuss 
people - Eleanor Roosevelt 
When any rule or formula becomes a substitute for thought rather than an aid to 
thinking, it is dangerous and should be discarded -Thomas William Phelps

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Maureen English
Sent: Saturday, May 05, 2012 10:53 AM
To: D'Hooge Freek
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: speeding up expdp

The database is Enterprise Edition.

I've tried the export with parallel=2, parallel=4 and parallel=6 and
all take about the
same amount of time.  I also set the database parameters
parallel_min_servers and
parallel_max_servers to 6 and 12, respectively.  I see the processes
start up in the
database at the beginning of the export, but I didn't check to see how
many were
still running when the export was done with everything but these last
2 tables.  I've
just been watching the dump files...only 2 are being written to at the end.

I do believe that both of these tables have a lob column.  Does that
mean I just have
to live with the 7.5 hours?  At least the import to the new machine
only takes about
1.5 hours.

I'm also excluding indexes/constraints for the export and import.

I'm pretty sure I read through the checklist document, but not the
other 2.  I'll
check them out.


- Maureen

On Fri, May 4, 2012 at 9:51 PM, D'Hooge Freek <Freek.DHooge@xxxxxxxxx> 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@xxxxxxxxx
> tel +32(0)3 451 23 82
> disclaimer:
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Maureen English
> Sent: zaterdag 5 mei 2012 3:58
> To: oracle-l@xxxxxxxxxxxxx
> 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
> --


Other related posts: