Re: datapump export incredibly slow

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 14 Feb 2014 14:10:45 -0800 (PST)
Message-ID: <1392415845.11321.YahooMailNeo_at_web124704.mail.ne1.yahoo.com>



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-l
Received on Fri Feb 14 2014 - 23:10:45 CET

Original text of this message