Re: Re: Using dblinks over distance
Date: Tue, 14 Jan 2020 15:07:40 +0000
Message-ID: <YQXPR01MB32397F911C8C99DB062AD0BAA6340_at_YQXPR01MB3239.CANPRD01.PROD.OUTLOOK.COM>
All the PS queries I see are usually doing nested loops due to heavy use of "exists" operators and the use of underscore parameters, but maybe the newer versions of PS have done away with this. Anyways, this can be problematic with db links. I take it you've played with "driving_site" hint? I remember in past versions "driving_site" hint didn't work with insert statements (maybe all DML) so a view was created on the remote site pointing back at the source db.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Cohen, Andrew M. <Andrew.Cohen_at_tufts.edu> Sent: January 14, 2020 9:50 AM
To: ahmed.fikri_at_t-online.de <ahmed.fikri_at_t-online.de>; oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: RE: Re: Using dblinks over distance
Thank you all for the suggestions and lessons in physics. I’ve been assured by our network team that the bandwith we have is about as good as we are going to get. Ping tests are showing 1 ms or less from on-prem to on-prem, while they are about 12 ms from on-prem to the cloud location. I was hoping to find Oracle (or in my case PeopleSoft) parameters they may assist with the ability to decrease the response time in our application. It appears there may be PeopleSoft tuning parameters that may help, time will tell. It also appears that some of our issues are just bad coding that didn’t surface until now. Again, thanks and if anyone has any experience with PeopleSoft tuning parameters that may assist our issue, feel free to share.
Andy
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of ahmed.fikri_at_t-online.de
Sent: Monday, January 13, 2020 10:34 AM
To: oracle-l_at_freelists.org
Subject: AW: Re: Using dblinks over distance
we use dblink to exchange data between many databases hosted in Europe (Data amount > 20 TB). Every things work fine (there is some limitations but still now every things work fine). However currently all our customers have the databases servers on the same location (luckily). We will see when one of them decides to use different locations (for example Europe/Asia). Maybe in this case would the cloud save our jobs
regards
Ahmed Fikri
Gesendet mit der Telekom Mail App<https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>
- Original-Nachricht --- Von: Mladen Gogala Betreff: Re: Using dblinks over distance Datum: 13.01.2020, 13:40 Uhr An: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
There are other solutions, too: materialized views cache data locally and speed up selects. Replication can finish the update locally and then propagate it to remote site in the background, without users waiting.
On 1/13/20 3:20 AM, Andy Sayer wrote:
> You have a few options:
> Make the distance between DBs closer (migrate the non cloud to the
> same data centre, or migrate the cloud back to your premises).
> Increase the signal velocity (better cables, fibre optics...)
> Increase the possible bandwidth (more routes, better fibre optics...)
> Utilise more of the bandwidth you have available (change your code to
> work so it is less chatty across the link - see where most of the chat
> is coming from and start from there, favour anything but nested loop
> joins from one DB to the other)
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 14 2020 - 16:07:40 CET