CODEWITHSUNDEEP
oracle-databaseperformancejoindblinkisolation
Vincent Menard
Vincent Menard

Reputation: 1

Performance balancing while querying in a remote database

We are working on 2 AIX 7 server and 2 Oracle databases 12.1.0.2. 1 database (called in this topic DB1) is our central PROD db. The second database (called in this topic DB2) is a production DB too, but for used for a non critical application.

We want to isolate traitement (impact as less as possible DB1) executed on DB2 (with joins) from the central production database DB1. These traitements uses DBLINK to read DB1 datas.

So the question is: If we perform a query like

select col1, col2 from table1@dblink_DB1, table2@dblink_DB1 where JOIN DB1/DB2

On which server the JOIN treatment is executed? Are only reads occurring on DB1 (so low performance case) and JOIN treatment is executed with SGA/CPU on DB2? Or is everything executing on DB1?

Upvotes: 0

Views: 76

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Such queries (which can be executed fully remotely, without access to local database) usually work on the remote db link site and it's much better than if it work on local database, since in this case it would read leading table and run (Select * from table@dblink_DB1 where col=:a) so many times as a number of rows returned from table1@dblink_DB2. Of course, you can force it run locally using hint driving_site, but this case it would be far less effective for both databases. Read more about driving_site hint. And also you should now that dml statements (update/delete/merge/insert) work always on the database where you change data.

Upvotes: 1

Related Questions