Re: Running import datapump over a database link.

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Fri, 8 May 2015 20:45:02 -0400
Message-ID: <CAAaXtLBuGPTucjbJL3f4zOHNQWuyoj_oVFB2WBdWom+=+hbk8Q_at_mail.gmail.com>



Mileage will vary on that, depending on the bandwidth and latency of "the wire". *Sometimes* transfer time across the network will not be a critical contributor to overall elapsed time, but that usually happens when the dataset is modest and the network is fast.

It is certainly not uncommon, though, for Jeremy's method to reduce overall runtime -- that is, that doing

  • export to disk
  • compress the export with gzip / bzip / whatever
  • transfer to remote site
  • uncompress
  • import

will often be faster than doing the export/import directly across a dblink. It all depends on how much data is being moved, and how fast the "wire" can move it.

[In this case, though, we don't really care about end-to-end elapsed time, just the elapsed time of the export, because that is what affects the risk of ORA-1555 errors. The multi-phase method is almost a guaranteed winner, although now that I think about it, the choice to compress the exported data or not will have NOTHING to do with the reduction in export-time. You'll get the same gain in the export phase either way.]

When you compress with bzip/gzip, you'll probably get 10:1 compression or better on a datapump export. That can make a big difference when moving data across a WAN, even if the WAN nominally does compression already.

(I am not a telecom expert, but I would expect wire-level compression has to work at the packet level. When you are compressing one packet -- maybe 1200 bytes -- at a time, its easy to imagine how the effectiveness of the compression may be limited. File compression tools like bzip/gzip are likely to work with much larger buffers, and therefore have more opportunity to compress data.)

The details are not especially important, though. The bottom line is, when you are moving large files over a slow network, you'll often do much better if you compress the files first. As soon as transfer time becomes a limiting factor in your export/import, then breaking the process down into components like Jeremy has suggested can greatly reduce the elapsed time of the "export" phase, and reduce the chances of ORA-1555. In fact, this will be true whether or not you compress the data before transferring it -- the critical gain (in this specific case) comes from decoupling network transfer time from the export phase. After that, *reducing* the network transfer time is just "icing on the cake".

Of course, opening a standby for READ ONLY and running the export there will eliminate the chances of ORA-1555 errors completely. But that's a completely different part of the same conversation. :-)

On Fri, May 8, 2015 at 6:48 PM, Jeremy Schneider < jeremy.schneider_at_ardentperf.com> wrote:

> > Jeremy Schneider wrote:
> >
> > > if I remember correctly, nothing is compressed when you data pump
> > > over a dblink.
> >
> On Fri, 8 May 2015 08:28:19 -0700 Yong Huang wrote:
> > Maybe you're referring to this?
> >
> > IMPDP using network_link ignores table compression in 10gR2 (Doc ID
> > 975822.1)
> >
> > The problem does not exist in 11g.

>

> Nope, I simply mean that data is not compressed on the wire as it's
> transferred over the network. When you compress a disk export then
> transfer the compressed files over the network, it can make a big
> difference (if I'm remembering correctly about compression on the wire
> - i.e. do your own testing).
>

> -Jeremy
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 09 2015 - 02:45:02 CEST

Original text of this message