In addition to optimizing the export (expdp), would try "wsize"
options for NFS mounted drive . Manipulation of this parameter may speed
up the write operation. It seems that the bottleneck in this case is NFS.
II had a similar case where a large file (2GB) was created by UTL_FILE.
Perform fewer flush operations and increase wsize, greatly accelerated
the write operation on NFS storage, also as I remeber NFS version (mount
option) for NFS may have performance affect .
Did you check export performance on local storage?
Pozdrawiam / Regards,
W dniu 05.05.2012 19:52, Maureen English pisze:
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_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
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
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.
On Fri, May 4, 2012 at 9:51 PM, D'Hooge Freekwrote:
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]
Oracle Database Administrator
tel +32(0)3 451 23 82http://www.uptime.be
From: email@example.com On Behalf Of Maureen English
Sent: zaterdag 5 mei 2012 3:58
Subject: speeding up expdp
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.
Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 0000021828,
dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku,
VIII Wydział Gospodarczy Krajowego Rejestru Sądowego,
o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych,
NIP: 586-000-78-20, REGON: 190024711--http://www.freelists.org/webpage/oracle-l