Re: Using dblinks over distance

Run Snapper on one of the sessions to see what kind of throughput you are getting and how many SQL*Net roundtrips per second you are doing.

Snapper reports metrics like these:

• bytes sent via SQL*Net to dblink
• bytes received via SQL*Net from dblink
• SQL*Net roundtrips to/from dblink

Arraysize only affects Oracle server <-> Client Application traffic, the database link arraysize is always set to max (32767 I think). So, setting your client fetch arraysize doesn't affect DBlinks. Assuming that you're trying to move large amounts of rows at a time (and not a PL/SQL loop querying one row per roundtrip), you'll need to tune your network/TCP stack for throughput:

1. Make sure that your app is moving large amounts of rows at a time (large INSERT-SELECTs or SELECTs moving millions of rows at a time)
2. Make sure that your network link/connection between the endpoints is actually capable of producing the throughput you need (use iperf3 app for testing)
3. Enable TCP send/receive buffer sizes to grow to the required size given the ACK latency and throughput requirement
   • Search for: "Bandwidth Delay Product"
   • Essentially: TCP send buffer = Desired Bandwidth * ACK Latency
4. "Tuning" Oracle SDUs & TDUs is probably not needed in modern Oracle versions and won't help at all if 1-3 haven't been solved first

There's usually little that can be done to reduce ACK latency because of network topology and physics

--
Tanel Poder
https://blog.tanelpoder.com/seminar


On Thu, Jan 9, 2020 at 11:29 AM Cohen, Andrew M. <Andrew.Cohen_at_tufts.edu>
wrote:



> As we move some of our database VMs to the cloud we have come across the


> following situation where the database links between databases (which used

> to be all on prem) are now being attempted over thousands of miles.  This

> has caused extremely poor performance.  We have found that an arraysize

> session parameter assists with some of the select statements, but updates

> which use links to get data don’t seem to perform any better with this

> arraysize parameter.  To make matters more complicated, we are making these

> calls in PeopleSoft applications.

>

>

>

> We’re using Oracle 18 and 19 databases on Rhel 7 mostly.

>

>

>

> Does anyone have any suggestions as to how to improve performance using

> database links over distance?

>

>

>

> Thanks,

>

> Andy

>


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