Re: Using dblinks over distance
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Sun, 12 Jan 2020 21:26:22 -0700
Message-ID: <CAMHX9JL=1+XHzL4ys49Rt1h_f6FpfrKrR=p01XyoHsqh_UOwQA_at_mail.gmail.com>
Date: Sun, 12 Jan 2020 21:26:22 -0700
Message-ID: <CAMHX9JL=1+XHzL4ys49Rt1h_f6FpfrKrR=p01XyoHsqh_UOwQA_at_mail.gmail.com>
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:
- 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)
- Make sure that your network link/connection between the endpoints is actually capable of producing the throughput you need (use iperf3 app for testing)
- 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
- "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:Received on Mon Jan 13 2020 - 05:26:22 CET
> 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