RE: speeding up expdp

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Sat, 5 May 2012 17:41:56 -0700
Message-ID: <D29F9902E534D5478F2E83FD6A44B30649BF83CE00_at_mail02.mba.xifin.com>



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 before. PARALLEL=12 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Maureen English Sent: Saturday, May 05, 2012 10:53 AM
To: D'Hooge Freek
Cc: oracle-l_at_freelists.org
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.

Thanks!

  • Maureen

On Fri, May 4, 2012 at 9:51 PM, D'Hooge Freek <Freek.DHooge_at_uptime.be> 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
>
>

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


--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 05 2012 - 19:41:56 CDT

Original text of this message