Re: speeding up expdp

From: Pawel Smolarz <pawel.smolarz_at_nordea.com>
Date: Mon, 7 May 2012 11:54:02 +0200
Message-ID: <4FA79BBA.5090800_at_nordea.com>



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_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
>
>

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 Received on Mon May 07 2012 - 04:54:02 CDT

Original text of this message