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

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 <david_at_david-aldridge.com> 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" <mwf_at_rsiz.com> 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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> 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.
>

-- 
Regards,
Ranko Mosic
Consultant Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
--
http://www.freelists.org/webpage/oracle-l