Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Poor performance on bulk transfer across db link -- wrapup

Re: Poor performance on bulk transfer across db link -- wrapup

From: Ranko Mosic <>
Date: Wed, 14 Nov 2007 18:08:50 -0500
Message-ID: <>

I second what Tanel said - by setting TCP buffer size properly you'll see huge increase in speed ( 7 times last time I did it ). Ranko.

On 11/14/07, David Aldridge <> wrote:
> Yes, I did think about copy, but there's an issue with password management
> there -- I couldn't think of a way (in our environment) to handle the
> passing of passwords in an acceptable manner. Also it's deprecated now, I
> believe. :(
> The servers are certainly in walking range, but getting physical access to
> them is a different matter -- we're buttoned down pretty tight here :)
> There does seem to be some magical SAN technology available to us though. We
> ought to be able, apparantly, to place the tablespaces we want to share
> around on dedicated mount points and make them available read-only to a
> large number of machines almost instantly. The disk bandwidth is shared
> among all the instances that import those tablespaces bu with clever query
> timing we ought to be able to leverage SAN-level caching to remediate that
> issue.
> Anyway, the crisis that we're currently deaing with is turning into a longer
> term benefit as it has forced the recognition and adoption of at least one
> technology (transportable tablespaces) that looks like it'll be beneficial
> in a lot of circumstances.
> "Mark W. Farnham" <> wrote:
> long thread I'm not sure whether anyone mentioned the alternative of
> sqlplus copy. Arraysize and copycommit control the ack nak and avoid
> creating a single gigantic transaction. You have to be careful about whether
> all the types you have are fully supported, and may sure your session LONG
> is set to at least the length of your longest long in a given table being
> copied. Since the monolithic transaction size is controlled it is reasonable
> to ramp up the number of concurrent sessions running copies to your desired
> level of saturation of cpus and network bandwidth. My experience is that
> running the copy of from the side you are pulling to works marginally
> better, but the last time a copied a *LOT* of data this way was when you
> could still time network database transport speeds with an analog
> wristwatch.
> Regarding other approaches, do not underestimate the speed of sneakernet.
> Are your servers within walking distance? Do they have compatible media?
> Good luck.
> ________________________________
> From: []
> On Behalf Of David Aldridge
> Sent: Thursday, November 08, 2007 11:25 AM
> To: Oracle List
> Subject: Re: Poor performance on bulk transfer across db link -- wrapup
> Thanks to everyone who responded.
> We did a bunch of testing on changing SDU, but aren't able to change the MTU
> due to the impact on other production systems. Reducing the SDU to match the
> MTU produced no performance change. DB Links just seem to be problematic for
> performance, maybe due to something in our own environment, but our timeline
> doesn't give us the opportunity to investigate further..
> We're looking at other solutions -- transportable talespaces, in fact.
> Thanks again.

Ranko Mosic
Consultant Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
Received on Wed Nov 14 2007 - 17:08:50 CST

Original text of this message