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

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:

        v\:* {behavior:url(#default#VML);}  o\:* {behavior:url(#default#VML);}  w\:* {behavior:url(#default#VML);}  .shape {behavior:url(#default#VML);}                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.

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