Re: speeding up expdp
- From: Pawel Smolarz <pawel.smolarz@xxxxxxxxxx>
- To: "maureen.english@xxxxxxxxxx" <maureen.english@xxxxxxxxxx>
- Date: Mon, 7 May 2012 11:54:02 +0200
Hi,
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,
Paweł Smolarz
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_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@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
>> http://www.uptime.be
>> disclaimer: www.uptime.be/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
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
> --
> //www.freelists.org/webpage/oracle-l
>
>
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--
//www.freelists.org/webpage/oracle-l
Other related posts:
