Re: datapump export incredibly slow
Date: Fri, 14 Feb 2014 16:16:36 -0600
Message-ID: <CAP79kiSk8Y-C21tZbZHj+STdKDwW7Y=UVhHA7The2Fn_Tx5Aeg_at_mail.gmail.com>
Great point David - his dump file should have a wildcard character in it I think.
DUMPFILE=xxxx_full_%U.dmp
On Fri, Feb 14, 2014 at 4:10 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
> I have to ask why you're running PARALLEL=4 and only creating one dump
> file? It may be that the parallel processes running for the export are
> waiting for a coordinator to consolidate query results and also the
> parallel write processes may get into a queue to write to the singleton
> dump file. Such actions could very well result in the waits you're seeing
> where 'nothing' is being done.
>
> Can you try using as many dump files as parallel processes (4)? Your
> parfile list would then look like this:
>
> SCHEMAS=xxxxxxx
> CONTENT=ALL
> DUMPFILE=xxxxxxx_full1.dmp, xxxxxxx_full2.dmp, xxxxxxx_full3.dmp,
> xxxxxxx_full4.dmp
> LOGFILE=xxxxxxxx_full.exp.log
> CLUSTER=N
> PARALLEL=4
> COMPRESSION=ALL
> FLASHBACK_TIME=SYSTIMESTAMP
> EXCLUDE=STATISTICS
>
> This might mitigate the issues you report.
>
> David Fitzjarrell
> Primary author, "Oracle Exadata Survival Guide"
>
>
> On Friday, February 14, 2014 2:01 PM, "Stephens, Chris" <
> Chris.Stephens_at_adm.com> wrote:
> 11.2.0.4 RAC on Oracle Linux 6.
>
> Right now I’m battling an unbelievably slow export but imports seem
> unreasonably slow as well. This occurs in two different RAC environments.
> I have a ticket open with Oracle but that’s not getting me anywhere quite.
>
> I’m exporting with the following parameters:
>
> SCHEMAS=xxxxxxx
> CONTENT=ALL
> DUMPFILE=xxxxxxx_full.dmp
> LOGFILE=xxxxxxxx_full.exp.log
> CLUSTER=N
> PARALLEL=4
> COMPRESSION=ALL
> FLASHBACK_TIME=SYSTIMESTAMP
> EXCLUDE=STATISTICS
>
> It looks like the export does some work (lots of direct path reads) for a
> while and then does nothing with tons of waits similar to:
>
> *** 2014-02-14 14:51:25.924
> WAIT #139990593434552: nam='wait for unread message on broadcast channel'
> ela= 1000810 channel context=93982405280 channel handle=94519841120 p3=0
> obj#=803670 tim=1392411085924778
>
> *** 2014-02-14 14:51:26.925
> WAIT #139990593434552: nam='wait for unread message on broadcast channel'
> ela= 1000867 channel context=93982405280 channel handle=94519841120 p3=0
> obj#=803670 tim=1392411086925789
> EXEC
> #139990593434552:c=2999,e=5009559,p=0,cr=4,cu=0,mis=0,r=0,dep=3,og=4,plh=0,tim=1392411086926186
> ERROR #139990593434552:err=25228 tim=1392411086926229
> CLOSE #139990593434552:c=0,e=4,dep=3,type=3,tim=1392411086926370
>
> I’m guessing that error possibly indicates a bug?
>
> The export is estimated to be ~80GB. I let it run for several hours this
> morning and it was nowhere close to being done.
>
> Anybody have any ideas or seen this before?
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or entity to which
> it is addressed and may contain information that is privileged,
> confidential and exempt from disclosure under applicable law. If the reader
> of this message is not the intended recipient or the employee or agent
> responsible for delivering this message to the intended recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify us immediately by email reply.
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 14 2014 - 23:16:36 CET