DA Morgan wrote:
> Snonck wrote:
>>
>>
>> Brian Peasland wrote:
>>> Snonck wrote:
>>>> Are many people using datapump (expdp) on solaris 10, oracle
>>>> 10.2.0.2 or on other platforms?
>>>>
>>>> I find that it is really slow! It sits there for ages doing nothing,
>>>> well nothing obvious. I was doing some tracing and found that it was
>>>> doing the following for every single user in the database!
>>>>
>>>> SELECT COUNT(*) FROM ALL_USERS WHERE USERNAME = :B1
>>>>
>>>> I guess it must have a good reason for doing it?
>>>>
>>>> It also does lots of other SQL like the above statement. Once it
>>>> starts doing the table dumps it is quite good, especially the
>>>> parallelism, but at the beginning and end when it does the meta data
>>>> it takes ages (I realise that it does the meta data sequentially but
>>>> still it takes ages)! Especially when you have over a hundred
>>>> thousand users in the database.
>>>>
>>>> I'm going to do some comprehensive tests later on, so might post
>>>> some timings then.
>>>
>>> I have noticed that expdp does take some "think time" before data
>>> actually starts getting dumped. But is this really slow? Typically,
>>> not for me. But I guess the more objects you have to export, the
>>> longer this may take. How long is "ages"?
>>
>> Did some tests today. I dumped a 40Gb database in about 2 hours using
>> exp on a 8.1.7.4 database. I then upgraded the database to 10.2.0.2,
>> using the dbua, and then did a expdp that took about 5 hours (both on
>> the same machine)! There is something definitely going wrong, but I
>> haven't had enough time to try a few more things yet. The most time is
>> spent dumping the meta data. The actual table data owned by the users
>> is dumped quite fast. I started a meta data dump before I left tonight
>> and it was going so slow.
>>
>>
>>>
>>> Cheers,
>>> Brian
>
> Given that you said "upgraded" I would take a look at any legacy
> parameters that might be holding you back. Also, it might be helpful,
> for us to see your DataPump instructions.
It seems obvious that I'm hitting some bug or that maybe it isn't that
simple a problem because there doesn't seem to be much information on
datapump performing badly :( When I get some more time I'll look into it
in more depth. I just thought if there was something simple I had missed
then people could give me some quick tips but maybe not many people are
actually using datapump yet?
The datapump settings I used were:
filesize=3G
parallel=18
directory=expdp1
dumpfile=expdp1:expdp_full_UPGR_1_%U.dmp
,expdp2:expdp_full_UPGR_2_%U.dmp
,expdp3:expdp_full_UPGR_3_%U.dmp
#content=metadata_only
full=y
status=30
logfile=expdp_full_UPGR.log
I am getting lots of waits as follows, but I have to read up a lot on
these as I haven't come across them before:
PX Deq Credit: need buffer
PX Deq: Execute Reply
Streams AQ: waiting for time
Streams AQ: qmn coordinator i
Streams AQ: qmn slave idle wa
PX Deq: Table Q Normal
PX Deq Credit: send blkd
wait for unread message on br
Received on Wed Nov 22 2006 - 16:52:00 CST
