3 Replies Latest reply: Nov 17, 2012 8:12 AM by Mark D Powell RSS

    export of instance takes long time

    895623
      Hi,

      we're exporting an instance of a 10g database and it takes incredible long time. The dba told me, he sees wait/io on db file sequential read.

      I've tried to break the issue down on the OS side (Solaris 10 U09 Sparc,Generic_147440-09 ). The process ora_dw01_testinstance is doing all the work reading the source.
      It uses 8k preads. Between 500 - 700 iops are transferred to the storage and replied, with a transfer rate from 5 - 8 mb/sec. Disk latency is around 4 - 10 ms.

      When looking on the microstats of the process it's mainly on sleep (over 90 %) . I've started 3 dd calls with different blocksizes on different db files in the same filesystem. Although the files have not been in any cache, the storage delivered around 70 - 85 mb/sek, even with a 8k blocksize. Well, the latency was much more higher when the dd processes were running. But I'm still wondering why the export is working that poor.

      Are there ways to get the read phase of the export improved?

      thank you,

      solst_ice

      Edited by: 892620 on Nov 17, 2012 5:49 AM

      Edited by: 892620 on Nov 17, 2012 6:00 AM
        • 1. Re: export of instance takes long time
          Srini Chavali-Oracle
          Pl post the exact export command being used and the exact database version. Are you using classic exp or expdp ?

          Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) [ID 453895.1]
          Master Note for Export and Import [ID 1264691.1]
          Getting 10046 Trace for Export and Import [ID 258418.1]
          Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1]

          HTH
          Srini
          • 2. Re: export of instance takes long time
            Mark D Powell
            First off, which export utility are you using: expdp or exp? What parameters have been specified? Also 10g is a marketing lable and not a version of Oracle so what is the exact version of Oracle in use? There are more than 60 reports on Oracle support about datapump performance issues but most are Oracle version and feature specific.

            If using datapump the following support document should be of interest:
            Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) [ID 453895.1]

            If using exp then did you provide a buffer= parameter?

            HTH -- Mark D Powell --
            • 3. Re: export of instance takes long time
              895623
              Hi,

              that were fast replies -)

              It's runnig 10.2.0. .
              expdp is used with these params:

              DIRECTORY=EXPORTBLOB
              DUMPFILE=exp_testinstance3%U.dmp
              LOGFILE=exp_testinstance.log
              FLASHBACK_TIME="TO_TIMESTAMP(sysdate)"
              #PARALLEL=6
              SCHEMAS=WEBAPP

              I'll check the mentioned infodocs next.

              thank you,

              solst_ice