This discussion is archived
9 Replies Latest reply: Feb 6, 2013 9:58 PM by arnon82 RSS

expdp without estimate

arnon82 Newbie
Currently Being Moderated
Hi,
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 11.2.0.2 linux redhat

will appreciate any help,
Arnon

Edited by: arnon82 on Jan 31, 2013 10:22 PM
  • 1. Re: expdp without estimate
    Dean Gagne Expert
    Currently Being Moderated
    Hi,

    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 :^)

    Dean
  • 2. Re: expdp without estimate
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    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 11.2.0.2 but something is wrong. For a 700GB database with 2000 tables the estimate is taking perhaps 2 minutes for us with 11.2.0.3. Its dependant on the type and number of objects of course and how fast various dictionary queries are.

    Regards,
    Harry
  • 3. Re: expdp without estimate
    TSharma-Oracle Guru
    Currently Being Moderated
    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.
  • 4. Re: expdp without estimate
    User286067 Journeyer
    Currently Being Moderated
    Hi Dean, perhaps collecting stats on SYS schema would help here?
  • 5. Re: expdp without estimate
    Dean Gagne Expert
    Currently Being Moderated
    collecting stats may help, but not always. I would check with Oracle Support to see if there is a fix for the version you are running.

    Dean
  • 6. Re: expdp without estimate
    arnon82 Newbie
    Currently Being Moderated
    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.
  • 7. Re: expdp without estimate
    User286067 Journeyer
    Currently Being Moderated
    arnon82 wrote:
    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.
    Ahem, what do you mean by full stats ? Can you share the command used?
  • 8. Re: expdp without estimate
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Take a look on MOS at:

    Bug 12780993 - Poor Datapump EXPDP performance for ESTIMATE phase [ID 12780993.8]

    You can possibly apply one off patch 12780993 to resolve that if that is the cause.

    Cheers,
    Harry
  • 9. Re: expdp without estimate
    arnon82 Newbie
    Currently Being Moderated
    Hi,
    i used the following statement for collecting stats:
    exec DBMS_STATS.gather_database_stats

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points