Data Pump slower than equivalent Export ?!?

From: <mccmx_at_hotmail.com>
Date: 16 Sep 2005 07:19:29 -0700
Message-ID: <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.

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

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 Received on Fri Sep 16 2005 - 09:19:29 CDT