9 Replies Latest reply: Feb 6, 2013 11:58 PM by arnon82 RSS

    expdp without estimate

    arnon82
      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-Oracle
          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 .
            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
              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
                Hi Dean, perhaps collecting stats on SYS schema would help here?
                • 5. Re: expdp without estimate
                  Dean Gagne-Oracle
                  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
                    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
                      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 .
                        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
                          Hi,
                          i used the following statement for collecting stats:
                          exec DBMS_STATS.gather_database_stats