Re: User Experiences with Data Pump Versus Legacy EXP/IMP

From: EdLong <rdhm99a_at_prodigy.net>
Date: Sat, 1 Mar 2008 09:57:28 -0800 (PST)
Message-ID: <f3ed00db-bb43-4318-b97b-2dbc94e9f971@u10g2000prn.googlegroups.com>


On Feb 26, 1:22 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Feb 25, 1:15 pm, EdLong <rdhm..._at_prodigy.net> wrote:
>
>
>
> > On Feb 25, 1:26 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > On Feb 24, 5:08 pm, EdLong <rdhm..._at_prodigy.net> wrote:
>
> > > > Hi everyone.
> > > > We are 10.2.0.3 running on Windows 2003. I have to move about a
> > > > terabyte of Oracle data from one disk array to another; its split
> > > > amongst 5 schemas or so. I'd like to solicit some user experiences
> > > > with DataPump. You may infer that we have not had the best experience
> > > > so far.
>
> > > > I started with the largest subset, a single schema of about 200gb.
> > > > Using traditional exp, sneaker net, import, this took about 24 hours
> > > > to move. Of this, nearly 20 hours was the import. Target machine
> > > > consists of 4 3ghz engines with beaucoup memory and little if any
> > > > competing workload.
>
> > > That seems slow, have you done everything you can to speed it up?http://www.oracledba.co.uk/tips/import_speed.htmInparticular, using
> > > direct path for both exp and imp, and doing the imp with noarchivelog,
> > > then switching to archivelog and taking a backup can make a big
> > > difference. Have you checked to see what is slowing it down?
> > > Sometimes the I/O is just overwhelmed, especially if you are dependent
> > > on some RAID-5 with write buffers that get saturated.
>
> > > > I've tried several different cuts at DataPump but found it somewhat
> > > > unsatisfactory.
>
> > > > It appears that at least in our shop at this level, The target
> > > > directory for the expdp and the source directory for the impdp cannot
> > > > be either a mapped drive or a USB drive. I could not find any
> > > > documentation for these apparent limitations.
>
> > > > The expdp says I can't open and write to the log file if its a map
> > > > drive. The EXPDP appears to work when pointed at a USB drive. The
> > > > impdp fails on a USB drive with a 'hard' i/o error after an hour or
> > > > two of processing. I tried the latter at a parallel level of 4 and 2;
> > > > I didn't try 1.
>
> > > > What leads me to risk the wrath of Achilles and query this group is
>
> > > Aw, all we want are decent questions! :-)
>
> > > jg
> > > --
> > > @home.com is bogus.
> > > What's in your database? http://ap.google.com/article/ALeqM5ghPenZUJTE7BfSfgQbj6RX597DEAD8V019...quoted text -
>
> > > - Show quoted text -
>
> > Thank you both for the great suggestions.
> > 1: I'm confident the USB drive is ok; brand new disk, just formatted
> > and chkdsk /f'd with no problems. Not sure if its a Windows problem; I
> > have seen similar 'dodgy' results when Windows tried to compress a
> > file that ended up > 32Gb. No compression here though.
> > 2: I tried the noarchivelog option on the destination; it made a
> > slight difference.
> > 3: The target disk is an EMC Clarion CX-310 configured as a Raid 5
> > disk so Raid delay could be a factor.
> > 4: I don't control the source instances so CONSISTENT=Y and DIRECT=Y
> > were both problematic due to Undo size and other factors. I actually
> > went back to the original dump sent in by a customer some months ago
> > making the Export moot for the first schema. I'm retrying Direct and
> > consistent for the second schema.
> > 5: Thank you Jg for the link to the best practices stuff on export/
> > import. I've actually been considering a variant on this idea,
> > breaking up the export by tablesize then doing a series of imports
> > much like you describe.
> > 6: In watching the Import run the single gating issue appears to be
> > that its single threaded. The HBA gets to about 35% busy and stays
> > there. 35% busy, is right at the point in a single server queue where
> > there is always something using the queue but little queueing(sp?).
> > The processors on the target server are loafing.
>
> Sounds like you may be simply hitting your hardware limits then,
> though if noarchivelog doesn't make much difference, I'm not sure what
> is going on (unless your arcs go to a different device or
> controller?). You might try "roll-your-own-parallelization" by simply
> starting up a couple of imps to different tablespaces and see what
> your HBA and clock time say about that. I would expect your business
> would stay the same and the queues would grow, but I really don't know
> anything about Clarion, it may have some smartypants adaptiveness that
> you need to convince it to use, or may be much worse. If the latter,
> I would suspect the results mixadba posted wouldn't apply to your
> situation.
>
> DIRECT should be faster.
>
> And now for something completely different: http://asktom.oracle.com/tkyte/flat/
>
> jg
> --
> @home.com is bogus.
> Download Jonathan Lewis's its_the_data in rtf format. Open it with
> the notepad editor. Smoothly scroll left and right until you see
> square white blocks on either side, then moderately quickly scroll
> left and right between those areas. See the Pong?

Hi Everyone.
Thank you for the great suggestions. My progress report: 1: The suggestion about hardware limits got me thinking. So, I broke the mirror on the C: drive and used the now spare disk as a third disk to store tablespaces. This seems to have improved import performance. 2: I changed the tablespace creates to be gigantic thinking that extent management could be a factor.
3: I'm going to do some comparisions between the PATA drive test box and the CLarion enabled target system to see if the SAN makes any difference.
4: The import is still flaky; some tables import ok sometimes and fail other times with various errors, most commonly, precision failures. I did run all the upgrade stuff to get from 10.2 to 10.2.0.3. Progress at least on IMPORT. Thanks for the help. 1: I watched the PERFMON reports for Windows while the IMP ran. Received on Sat Mar 01 2008 - 11:57:28 CST

Original text of this message