Oracle 10.1.0.4 EE on RHEL 3

Oracle claim that Data Pump is a considerably faster method of loading
and unloading data to/from the database.

However my first 'play' with the tool shows the opposite:

1. Using Export:

time exp sysadm/**** file=job.dmp log=job.log tables=ps_job direct=y

.....
.....
About to export specified tables via Direct Path ...
.. . exporting table PS_JOB 261326 rows
exported
.....
.....
.....
Export terminated successfully with warnings.

real 0m43.620s
user 0m1.640s
sys 0m1.120s

2. Using Data Pump:

rm /tmp/job.dmp

time expdp sysadm/**** dumpfile=job.dmp logfile=job.log tables=ps_job
directory=temp

.....
.....
Starting "SYSADM"."SYS_EXPORT_TABLE_01": sysadm/********
dumpfile=job.dmp
logfile=job.log tables=ps_job directory=temp
Estimate in progress using BLOCKS method...
Processing object type
TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 176 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type
TABLE_EXPORT/TABLE/GRANT/TBL_OWNER_OBJGRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
.. . exported "SYSADM"."PS_JOB" 143.3 MB
261326 rows
Master table "SYSADM"."SYS_EXPORT_TABLE_01" successfully
loaded/unloaded
************************************************** ****************************
Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
/tmp/job.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 15:15

real 3m3.248s
user 0m0.030s
sys 0m0.010s

Export takes 43 seconds and Data Pump takes over 3 minutes..!

95% of the delay in Data Pump appears to be in the step:

"Estimate in progress using BLOCKS method..."

What exactly is done during this step..?

Matt

On Fri, 16 Sep 2005 16:19:29 +0200, mccmx@hotmail.com wrote
(in article <1126880369.250882.129120@z14g2000cwz.googlegroups .com>):

> Oracle 10.1.0.4 EE on RHEL 3
>
> Oracle claim that Data Pump is a considerably faster method of loading
> and unloading data to/from the database.
<snip>
> Export takes 43 seconds and Data Pump takes over 3 minutes..!
>
> 95% of the delay in Data Pump appears to be in the step:
>
> "Estimate in progress using BLOCKS method..."
>
> What exactly is done during this step..?

Hi Matt,
I am quite sure there can be made some improvements in the data pump but for
a serious speed test you need to take a larger database. I just finished some
testing and was quite happy with it. impdp read 46GB of data from a source
database and stored in a target database in less than 2 hours, over a network
link (100Mb). The ftp session alone of the conventional expdat.dmp (49GB) of
the same database took 1 hour and 20 minutes...
Disappointing is the fact that only for the data impdp uses parallel workers
and not for the other objects. They are serialized and in my case that took
nearly 10 hours to complete. Conventional exp (full) took 10 hours to
complete.
So, I think impdp deserves some more testing ...

Completely missing are warnings about NLS problems. Conventional exp did not
much but it did give warnings ...


With kind regards / met vriendelijke groeten,
Ronald

http://ciber.nl
http://homepage.mac.com/ik_zelf/oracle

"Ronald Rood" <devnull@ronr.nl> wrote in message
news:0001HW.BF50D8FD021EBD40F0305550@news.individu al.net...
> On Fri, 16 Sep 2005 16:19:29 +0200, mccmx@hotmail.com wrote
> (in article <1126880369.250882.129120@z14g2000cwz.googlegroups .com>):
>
>> Oracle 10.1.0.4 EE on RHEL 3
>>
>> Oracle claim that Data Pump is a considerably faster method of loading
>> and unloading data to/from the database.
> <snip>
>> Export takes 43 seconds and Data Pump takes over 3 minutes..!
>>
>> 95% of the delay in Data Pump appears to be in the step:
>>
>> "Estimate in progress using BLOCKS method..."
>>
>> What exactly is done during this step..?
>
> Hi Matt,
> I am quite sure there can be made some improvements in the data pump but
> for
> a serious speed test you need to take a larger database. I just finished
> some
> testing and was quite happy with it. impdp read 46GB of data from a source
> database and stored in a target database in less than 2 hours, over a
> network
> link (100Mb). The ftp session alone of the conventional expdat.dmp (49GB)
> of
> the same database took 1 hour and 20 minutes...
> Disappointing is the fact that only for the data impdp uses parallel
> workers
> and not for the other objects. They are serialized and in my case that
> took
> nearly 10 hours to complete. Conventional exp (full) took 10 hours to
> complete.
> So, I think impdp deserves some more testing ...
>
> Completely missing are warnings about NLS problems. Conventional exp did
> not
> much but it did give warnings ...
>
>
> With kind regards / met vriendelijke groeten,
> Ronald
>
> http://ciber.nl
> http://homepage.mac.com/ik_zelf/oracle
>

Today, I did some tests with Data Pump on AIX 5.2 (Oracle 10.1.0.4). First
impressions ? Not so good. Lots of ORA-00600 errors during a full export, or
an export of only metadata. I opened a TAR and Oracle is investigating this.
And yes, I have read the docs ;-) Other issue : to be able to perform a full
export, a user does not only need EXP_FULL_DATABASE, but also CREATE TABLE.
Second issue : expdp can't overwrite already existing dump files with the
same name ...

The "good old" exp works fine though.

Matthias Hoys

On Fri, 16 Sep 2005 20:18:05 +0200, Ronald Rood wrote:

> The ftp session alone of the conventional expdat.dmp (49GB) of
> the same database took 1 hour and 20 minutes...

You should have used scp with "-C" argument. It turns the compression on
and copies much faster then ftp ever could. If you are enamored by the
ftp's put/get/mput/mget/prompt command set, you can use sftp. In my
opinion, ftp and kermit commands are from the same period and both suck.
I use scp whenever I can.
As for "data pump" utilities, their main advantage comes from the fact
that they're able to use parallelism. Expdp can, in contrast to exp, use
parallel query, while impdp can do parallel DML. In order to see an
improvement, one needs to use a database which can utilize parallelism.



--
http://www.mgogala.com

Back to Orignal Question :

Direcy = y has been used with normal exp.

On Fri, 16 Sep 2005 21:47:42 +0200, Matthias Hoys wrote
(in article <432b215e$0$29281$ba620e4c@news.skynet.be>):

>
> "Ronald Rood" <devnull@ronr.nl> wrote in message
> news:0001HW.BF50D8FD021EBD40F0305550@news.individu al.net...
>> On Fri, 16 Sep 2005 16:19:29 +0200, mccmx@hotmail.com wrote
>> (in article <1126880369.250882.129120@z14g2000cwz.googlegroups .com>):
>>
>>> Oracle 10.1.0.4 EE on RHEL 3
>>>
>>> Oracle claim that Data Pump is a considerably faster method of loading
>>> and unloading data to/from the database.
>> <snip>
>>> Export takes 43 seconds and Data Pump takes over 3 minutes..!
>>>
>>> 95% of the delay in Data Pump appears to be in the step:
>>>
>>> "Estimate in progress using BLOCKS method..."
>>>
>>> What exactly is done during this step..?
>>
>> Hi Matt,
>> I am quite sure there can be made some improvements in the data pump but
>> for
>> a serious speed test you need to take a larger database. I just finished
>> some
>> testing and was quite happy with it. impdp read 46GB of data from a source
>> database and stored in a target database in less than 2 hours, over a
>> network
>> link (100Mb). The ftp session alone of the conventional expdat.dmp (49GB)
>> of
>> the same database took 1 hour and 20 minutes...
>> Disappointing is the fact that only for the data impdp uses parallel
>> workers
>> and not for the other objects. They are serialized and in my case that
>> took
>> nearly 10 hours to complete. Conventional exp (full) took 10 hours to
>> complete.
>> So, I think impdp deserves some more testing ...
>>
>> Completely missing are warnings about NLS problems. Conventional exp did
>> not
>> much but it did give warnings ...
>>
>>
>> With kind regards / met vriendelijke groeten,
>> Ronald
>>
>> http://ciber.nl
>> http://homepage.mac.com/ik_zelf/oracle
>>
>
> Today, I did some tests with Data Pump on AIX 5.2 (Oracle 10.1.0.4). First
> impressions ? Not so good. Lots of ORA-00600 errors during a full export, or
> an export of only metadata. I opened a TAR and Oracle is investigating this.
> And yes, I have read the docs ;-) Other issue : to be able to perform a full
> export, a user does not only need EXP_FULL_DATABASE, but also CREATE TABLE.
> Second issue : expdp can't overwrite already existing dump files with the
> same name ...
>
> The "good old" exp works fine though.
>
> Matthias Hoys
>
>
>

I also noticed some problems during expdp ('illegal use of LONG datatype' for
example ... That's why I started with impdp directly from the source database
over a network link. Quite powerfull, if it works... It is sensitive for NLS
settings and I did get data corruption in some cases that where tracked back
to be caused by an insert into a table from a remote table. It looks like
impdp first creates the table and does the insert afterwards.
In the end I also used exp/imp to do the job but I will sure check for
improvements in release 2.
possible improvements are
1) better compatibility check for NLS settings
2) get rid of the 'invalid use of LONG datatype in expdp
3) get rid of the datacorruption - database link issue
4) parallelism after the data move (procs, indexes etc)
5) a summary of errors encountered in the end of the log
6) keep the filesize limit when adding extra files to expdp

impdp already is fast but still can be lots faster - and better. It's as good
as te weakest link, in my case the database link.

With kind regards / met vriendelijke groeten,
Ronald

http://ciber.nl
http://homepage.mac.com/ik_zelf/oracle

Ronald Rood apparently said,on my timestamp of 17/09/2005 8:44 PM:
>>
>>Today, I did some tests with Data Pump on AIX 5.2 (Oracle 10.1.0.4). First
>>impressions ? Not so good. Lots of ORA-00600 errors during a full export, or
>>an export of only metadata. I opened a TAR and Oracle is investigating this.
>>And yes, I have read the docs ;-) Other issue : to be able to perform a full
>>export, a user does not only need EXP_FULL_DATABASE, but also CREATE TABLE.
>>Second issue : expdp can't overwrite already existing dump files with the
>>same name ...
>>
>>The "good old" exp works fine though.
>>
> I also noticed some problems during expdp ('illegal use of LONG datatype' for
> example ... That's why I started with impdp directly from the source database
> over a network link. Quite powerfull, if it works... It is sensitive for NLS
> settings and I did get data corruption in some cases that where tracked back
> to be caused by an insert into a table from a remote table. It looks like
> impdp first creates the table and does the insert afterwards.
> In the end I also used exp/imp to do the job but I will sure check for
> improvements in release 2.
> possible improvements are
> 1) better compatibility check for NLS settings
> 2) get rid of the 'invalid use of LONG datatype in expdp
> 3) get rid of the datacorruption - database link issue
> 4) parallelism after the data move (procs, indexes etc)
> 5) a summary of errors encountered in the end of the log
> 6) keep the filesize limit when adding extra files to expdp

Far out! I'm soooooo glad we stayed away from 10.1....
No wonder so few people use this thing for critical dbs!

--
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k@yahoo.com.au.nospam

On Sun, 18 Sep 2005 00:14:11 +1000, Noons wrote:

> Far out! I'm soooooo glad we stayed away from 10.1....
> No wonder so few people use this thing for critical dbs!

Oracle10g, release 10.3 may actually be usable.

--
http://www.mgogala.com

ms w2k adv svr
10.1.0.4
workspace manager was installed by default, was version 10.1.0.2.
an upgrade of workspace manager to 10.1.0.5 (or removal of the app) got
expdp working for me (with the help of an iTAR).

10.1.0.4 on RHEL 3 ES update 4 (x86), data pump export had no issues
with either expdp or impdp.

guess that its a platform-specfic thing.

-bdbafh

<bdbafh@gmail.com> wrote in message
news:1126967516.917050.181580@g47g2000cwa.googlegr oups.com...
> ms w2k adv svr
> 10.1.0.4
> workspace manager was installed by default, was version 10.1.0.2.
> an upgrade of workspace manager to 10.1.0.5 (or removal of the app) got
> expdp working for me (with the help of an iTAR).
>

What's workspace manager and what is its relation with Data Pump ?