I'm trying to do an expdp of full database.
the problem is that the whole expdp takes 2 hours - and from that 2 hours the estimate part take 40 minutes - which sounds unreasonable.
I'm trying to find a way to cut the estimate time. i tried to do the estimate with statistics instead of blocks but it didn't help.
is there another possible solution for that? i'm willing to skip the estimate part completely but i didn't find a way to do that.
the db version i use is 18.104.22.168 linux redhat
The estimate phase is a critical part of the export job. It is not only estimating the amount of data that is being exported. It is actually gathering data that is describing what we call TABLE_DATA objects. These are objects where data is stored. Let me explain:
for a table, it is the table itself
for a partitioned table, it is each partition
for a subpartitioned table, it is each subpartition
So, Data Pump starts by collecting this information. It then stores all of this in the Master Table so the job knows what data needs to be exported. This "phase" needs to be done sooner or later, we do it at the beginning of the job in case you specify parallel. Once this data is collected, parallel worker processes can start to unload data. If this was done later, the job would not begin parallel operation until this was collected. If the parallelism of the job is 1, then this could be done later, but it still needs to be done.
Now, having said all of that, there have been some performance in this area from release to release. I'm not sure what version you are running, but you could check with Oracle Support to see if there is a patch available for your version.
Hope this explains things a bit and helps.... a little :^)
As Dean syas there is no getting round this step but there are lots of bugs with it in early versions, not sure if specfically with 22.214.171.124 but something is wrong. For a 700GB database with 2000 tables the estimate is taking perhaps 2 minutes for us with 126.96.36.199. Its dependant on the type and number of objects of course and how fast various dictionary queries are.
Check your AWR report while running datapump. Check if datapump is using RBO or CBO. This was a bug in 10g and because of this bug Datapump starts using RBO instead of CBO which makes your datapump very slow in estimating.
This could be the bug in 11g too. Check this in AWR report.
thanks all for your help.
i tried to collect full stats but it still didn't change the situation and i still get very long estimate process time.
i will try to check with oracle if there's a solution for it.