Re: datapump export incredibly slow

From: Gus Spier <gus.spier_at_gmail.com>
Date: Sun, 16 Feb 2014 00:12:37 -0500
Message-ID: <CAG8xnieXf90R=UJtgmp96g+BGGgexrjv6ebz7QrADAErt7DzNg_at_mail.gmail.com>

Chris, is there anything significant about the object?

• 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

My limited understanding is that ela refers to the elapsed time and the tim value
represents the wall clock.

• 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

The next wait message cites the same WAIT # and name (wait for unread ...) The ela value is a mere 57 "oh-no seconds" since the last message but the wall clock (time) differs by 1001408 (unless there is a transcription error). The ERROR line assigns the same value as the WAIT#, EXEC#, and ERROR #. The error itself appears to be an ORA-25228. No surprise here - ORA-25228: timeout or end-of-fetch during message dequeue from string.string Cause: User-specified dequeue wait time has passed or the end of the queue has been reached but no message has been retrieved.  Action: Try dequeue again with the appropriate WAIT_TIME or the FIRST_MESSAGE option.

I wish I could be more help, but this might lead you to more lines of investigation.

Let us know how it works out.

Regards,

Gus

On Fri, Feb 14, 2014 at 4:00 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 Sun Feb 16 2014 - 06:12:37 CET