Skip to Main Content
  • Questions
  • Data Pump Export (expdp) is taking too long specially at the estimate step, how to skip estimate part?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manoj.

Asked: August 16, 2021 - 6:38 am UTC

Last updated: August 17, 2021 - 2:57 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

Finally, I was able to convince my clients to switch from the old "exp" utility to the new Data Pump "expdp" utility for exporting out a DB Dump file that can be used to move data into a Test DB.
We used to do export out the main schema of our application using the following script:
D:\Oracle\11g\Server\BIN\exp user/pwd@SID owner=schema_name buffer=65536 file=F:\DBDumps\exp_schema.DMP log=F:\DBDumps\exp_schema.log 


Now I am using the following script for Data Pump utility:
D:\Oracle\11g\Server\BIN\expdp user/pwd@SID schemas=schema_name directory=EXPDP_DB_DMPS dumpfile=expdp_schema.dmp logfile=expdp_schema.log reuse_dumpfiles=y


It took over 3 hours to estimate before it started exporting the data and then it took just 1 hour to actually export out around 125 GB data.

By default it used BLOCKS for estimating the export size.

We run gather statistics script every week. So I even tried using STAISTICS for estimating the export size, using following script:
D:\Oracle\11g\Server\BIN\expdp user/pwd@SID schemas=schema_name directory=EXPDP_DB_DMPS dumpfile=expdp_schema.dmp logfile=expdp_schema.log estimate=STATISTICS reuse_dumpfiles=y


The result was not too different, the estimate part still took long.

Then I used the following script:
D:\Oracle\11g\Server\BIN\expdp user/pwd@SID schemas=schema_name directory=EXPDP_DB_DMPS dumpfile=expdp_schema.dmp logfile=expdp_schema.log reuse_dumpfiles=y exclude=STATISTICS


I was hoping it would skip the estimate step, but it did not.

The actual export part is not taking long, it is the estimate part that is taking around 3 hours.

Can we speed up the estimate part or can we skip it altogether as we already know the space requirements?

I have tried searching on Google and also on Ask Tom, but have not seen anything regarding this.

I am also thinking of using "PARALLEL" parameter, but being new to expdp, I am not sure if this will help.


and Connor said...

If you are on 11.2.0.1 then you are likely hitting

Bug 12780993 - Poor Datapump EXPDP performance for ESTIMATE phase

which was fixed in 11.2.0.4.

Even 11.2.0.4 is out of support (I mean, seriously...11.2 was released in 2009!!!).

I'd strongly recommend you look at upgrading to 19c

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.