How to Tune SQL with DB Link for Oracle II?

Here is an example SQL, the query is retrieving employee, department, and grade tables from the remote database @richdb

SELECT *
FROM emp_subsidiary@richdb a,

department@richdb,
grade@richdb​

WHERE emp_grade < 1200

AND emp_dept = dpt_id
AND emp_grade = grd_id​

ORDER BY emp_id

Here the following is the query plan of this SQL, it takes 15.92 seconds to finish. The first step of the query plan is ‘SELECT STATEMENT REMOTE’, it means the entire query will be execute on the remote database @richdb and the result will be sent back to the local database. The query plan is a little bit complicated and not easy to tell it is optimal or not. But one thing we can try if the query is partially executed in the local database @local.

In order to request that Oracle perform certain join operations in the local database, the SQL query must include at least one table that is executed in the local database. This allows the use of the hint /*+ DRIVING_SITE ( [ @ queryblock ] tablespec ) */ in the SQL query. If no tables are explicitly executed in the local database, there is no means to request that Oracle attempt to perform join operations in the local database.

Let’s added a dummy condition “EXISTS (SELECT ‘X’ FROM DUAL)” and a hints /*+ DRIVING_SITE(DUAL) */ to the SQL to force Oracle to execute some join operations in the local database.

SELECT /*+ DRIVING_SITE(DUAL) */ *
FROM emp_subsidiary@richdb a,

department@richdb,
grade@richdb​

WHERE emp_grade < 1200

AND emp_dept = dpt_id
AND emp_grade = grd_id
AND EXISTS ( SELECT 'x'
FROM dual)​

ORDER BY emp_id

Below is the query plan for the modified SQL, which takes 4.08 seconds and is approximately 4 times faster than the original SQL statement where only one join operation is performed in the remote database.

Adding an ORDERED hint to the SQL query can result in further optimization. This will break down the compound statement highlighted in the previous query plan into individual table data remote extraction, as shown in the following query plan.

SELECT /*+ DRIVING_SITE(DUAL) ORDERED */ *
FROM emp_subsidiary@richdb a,

department@richdb,
grade@richdb​

WHERE emp_grade < 1200

AND emp_dept = dpt_id
AND emp_grade = grd_id
AND EXISTS ( SELECT 'x'
FROM dual)​

ORDER BY emp_id



If you are familiar with Oracle Exadata, you may notice that the data retrieval process for REMOTE tables in remote database @richdb works similarly to that of the Exadata Storage Server.

It is important to remember that applying this technique to SQL queries with a DB Link is only beneficial in certain environments. For instance, it is ideal when the network speed is good, data traffic is not heavy, and the workload on the local database is low.

Tosska DB Ace for Oracle can automatically perform this type of rewrite, resulting in an SQL query that runs almost 10 times faster than the original.

Tosska DB Ace Enterprise for Oracle - Tosska Technologies Limited
DBAO Tune DB Link SQL - YouTube