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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Cohen, Andrew M. <Andrew.Cohen@xxxxxxxxx>
Sent: January 14, 2020 9:50 AM
To: ahmed.fikri@xxxxxxxxxxx <ahmed.fikri@xxxxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
<oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of ahmed.fikri@xxxxxxxxxxx
Sent: Monday, January 13, 2020 10:34 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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)
