Re: datapump, expdp, is very slow

From: Snonck <snonck_at_snog.com>
Date: Tue, 21 Nov 2006 23:18:36 +1000
Message-ID: <4562fc2c$0$11972$afc38c87@news.optusnet.com.au>

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
> 
> 
> 

Received on Tue Nov 21 2006 - 07:18:36 CST