MIME-Version: 1.0
In-Reply-To: <4814386347E41145AAE79139EAA398981CC0B5180C@ws03-exch07.iconos.be>
References: <CAAzCTjEZXGrRcagXdNQTrnDyf2Sf_3rUqDnKigX-NqZuuR_wBw@mail.gmail.com>
 <4814386347E41145AAE79139EAA398981CC0B5180C@ws03-exch07.iconos.be>
Date: Sat, 5 May 2012 09:52:55 -0800
Message-ID: <CAAzCTjEGS88q3cScr8t7-KNaMTs_LgoZngU2HnbH8MRAW0nypg@mail.gmail.com>
Subject: Re: speeding up expdp
From: Maureen English <maureen.english@alaska.edu>
To: "D'Hooge Freek" <Freek.DHooge@uptime.be>
Cc: "oracle-l@freelists.org" <oracle-l@freelists.org>
Content-type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: maureen.english@alaska.edu

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@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@uptime.be
> tel +32(0)3 451 23 82
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer
> -----Original Message-----
> From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Maureen English
> Sent: zaterdag 5 mei 2012 3:58
> To: oracle-l@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