Tuning A Query Using Database Link:

A query that uses a table from remote database is taking a while to process and we will tune that slow query to make it faster. There is no silver bullet to tuning query therefore, a little background on database link is required to understand why it is slow?

What is a database link? A Database Link is an Oracle object that can access objects from a remote database instance as long as the user has the proper grants, privileges and user accounts. Database links are good for small data move, you might consider using Export/Import or Data Pump for larger data sets. There are two types of db links available in Oracle. Public and Private are two types of database link available in Oracle. Detail regarding database link are found <<here >>

 A Query using DB link is slow when the table in remote database is huge and or network latency. A developer or DBA cannot do anything with Network latency therefore, you must tune your query or re-design your database design. Our environment for a demo, we have LOCAL_DB and REMOTE_DB instances. Local_DB has two tables named local_small_table and local_big_table. Similarly, REMOTE_DB has two tables remote_big_table and remote_small_table. The Local_DB has a database link named remote db links objects that links to REMOTE_DB. All the access and privileges requires are in place.

Why is this query slow?

SELECT lst.id,
  lst.name,
  rbt.id,
  rbt.zip,
  rbt,
  phone
FROM local_small_table lst remote_big_table@remote_dblink rbt
WHERE lst.id = rbt.id;

If both tables existed on a same db instance, the query will just run fine. This is a query that is taking very long time because the table on remote database is big.
How do you make this fast?

SELECT lst.id,
  lst.name,
  rbt.id,
  rbt.zip,
  rbt.phone
FROM local_big_table lst remote_small_table@remote_dblink rbt
WHERE lst.id = rbt.id;

This is a query that is taking very less time. This is because the table on remote database is small. This is how the database should be designed but this is not always the case in real life. If you look for Slow query above where the big table is on remote database. How are you going to solve the problem? there are quite a few ways we can make it run faster.

1. WITH CLAUSE 
2. DRIVING_SITE HINT

WITH CLAUSE:

WITH rbt AS
( SELECT id, zip, phone FROM remote_big_table@remote_dblink
)
SELECT lst.id,
 lst.name,
 rbt.id,
 rbt.zip,
 rbt.phone
FROM local_big_table, rbt
WHERE lst.id = rbt.id;

WITH CLAUSE also known as sub-query factoring clause, It allows you to give a name to a select query and use the result inside a query. WITH clause generally improves query performance by running the query once and use the result later. WITH common table expression is very popular on SQLServer world as CTE. WITH clause makes the query easier to read and understand by removing the query complexity. Oracle optimizes the query by treating the query name as either an inline view or as a global temporary table.

The big table from remote database is brought into local database instance and stored as a global temporary table. This is much faster compared to our slow query. The jest is to bring the big table's data to local database and then do the join sort etc. locally.
DRIVING_SITE HINT:

SELECT /*+ DRIVING_SITE(rbt) */ lst.id,
  lst.name,
  rbt.id,
  rbt.zip,
  rbt.phone
FROM local_small_table lst remote_big_table@remote_dblink rbt
WHERE lst.id = rbt.id;

When the remote table is huge, do the join on remote database and bring just the result over to local database. This can be done using DRIVING_SITE hint. Local Database may not have access to CBO on remote site and my perform a full table scan which is why your query is slow. The driving site hint forces query execution to be done at a different site from the originating instance. Again this should be used when the table on remote instance is bigger compared to local which does join, sort and everything that needs to happen remotely and save the trips and network traffic if it would have to be done on local instance.

We have just covered the SELECT query using db link. Next, we will look for ways to improve INSERT query using db link.

Slow Insert Query:

INSERT INTO local_big_table
  (id, name, zip, phone
  )
SELECT id,
  name,
  zip,
  phone
FROM remote_big_table@remote_dblink;

This query is slow because it has to make several round trips to get data from remote db instance. How do you reduce the trip? WITH CLAUSE or CURSOR are two ways I can think of to make it faster. They both get data to local server and store in memory or temp table and process from there.

CURSOR:

SET serveroutput ON
DECLARE
  CURSOR cur_emp
  IS
    SELECT id, name, zip, phone FROM remote_big_table@remote_dblink;
  l_cur_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO l_cur_emp;
    EXIT
  WHEN cur_emp%NOTFOUND;
  INSERT INTO local_big_table
 (id, name, zip, phone)
 VALUES(l_cur_emp.id, l_cur_emp.name, l_cur_emp.zip, l_cur_emp.phone);
  END LOOP;
  COMMIT;
  CLOSE cur_emp;
END;
/
baniya 

WITH CLAUSE:

WITH rbt AS
  (SELECT id, name, zip, phone FROM remote_big_table@remote_dblink
  )
INSERT INTO local_big_table
  (id, name, zip, phone
  )
SELECT id, name, zip, phone FROM rbt;

We identified why the queries using database links are slow and came with with multiple ways to make it way 10X if not 5X faster. I prefer WITH CLAUSE over DRIVING SITE but the choice is your and as always, experiment all and make decision that you like.