Re: datapump export incredibly slow
Date: Mon, 17 Feb 2014 12:18:39 +1100
Message-ID: <CAOEQsP+ad40kx-Gj9w8u-N9jCxT2X5vVuG0gbnJTu3aJGfEE0w_at_mail.gmail.com>
Some old ways from old imp are still useful in impdp, ex: 2-3 staged import -- table structure import first with no index and data, and re-structure the table to whatever you like, logging/nolooging/partition/storage clause, etc... import data, then execute index creation sqlfile generated by impdp (again, this sqlfile can be edited to include all the tuning tricks, ex parallel, nologging, session parameters). Selectively doing this to top few big tables will reduce impdp time a lot.
On Mon, Feb 17, 2014 at 11:21 AM, Sameer Choudhari < sameer.choudhari_at_gmail.com> wrote:
> Hi
>
> *Quick question:* is there any way we can speed up the IMPDP process by
> disabling the REDO log generation in Oracle 11g? I could see this feature
> is available in 12C. With *transform=disable_archive_logging *to disable
> all redo logging for Oracle 12c imports. Also, read about hidden parameter *_disable_logging
> = true* to reduce redo. But as per Tom's suggestion, never use this
> parameter as it corrupt the DB.
>
> Cheers,
> Sameer <http://www.choudhari.net>
>
>
> On 16 February 2014 16:12, Gus Spier <gus.spier_at_gmail.com> wrote:
>
>> 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-lReceived on Mon Feb 17 2014 - 02:18:39 CET