How to Tune SQL with DB Link for Oracle I?

Here is an example SQL query used to calculate the average salary of employees on the remote database @richdb in each department in the local database whose department name starts with the letter “D”.

SELECT   Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

Here the following is the query plan of this SQL, it takes 9.16 seconds to finish.  The query plan shows a Nested Loops from DEPARTMENT in local to EMPLOYEE in the remote database. Due to the size of the EMPLOYEE table being much larger than that of the DEPARTMENT table, the nested loop join path is not optimal in this case.

To ask Oracle to consider doing the join operation in the remote database @richdb, I added a Hint  /*+ DRIVING_SITE(employee) */ to tell Oracle to use EMPLOYEE table’s database @richdb as the driving site for the distributed query.

SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

The following query shows the driving site is changed to @richdb and remote retrieves DEPARTMENT data from the “local” database. Now the speed is improved to 5.94 seconds. But the query plan shows a little bit complicated, there is a view that is construed by a Hash Join of two “index fast full scan” of indexes from EMPLOYEE and DEPARTMENT.

I further change the SQL and added a dummy operation Coalesce(dpt_id,dpt_id) in the select list of the subquery to block the index fast full scan of the DEMPARTMENT table.

SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT Coalesce(dpt_id,dpt_id)
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

The change gives the SQL a new query plan shown in the following, the performance significantly improved to 0.71 seconds. You can learn how the dummy operation Coalesce(dpt_id,dpt_id) affected the Oracle SQL optimizer decision in this example.

This kind of rewrite can be achieved by Tosska DB Ace for Oracle automatically, it shows that the rewrite is almost 13 times faster than the original SQL.

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

Tosska DB Ace Enterprise (DBAO™) for Oracle® – System Requirements

Before installing Tosska DB Ace Enterprise (DBAO™) for Oracle®, please make sure your system meets the following minimum hardware and software requirements:

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 400 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5
Database Client Oracle® 9i Client
Oracle® 10G Client
Oracle® 11G Client
Oracle® 12c Client
Oracle® client is not required when Basic Type is used for Connection Mode
Database Server Oracle database 11.2 or higher version (11.2, 12.1 and 12.2)