- Whitehorses - http://blog.whitehorses.nl -

Better performance on database links

Tweet [1]

In my current project we are looking at different solutions to improve performance for our applications.

One of the issues is that we have an APEX application running on database X. Data for this application is partially pulled from a SIEBEL database Y using a database link.
When looking through the explain plans, we noticed that in some queries the data is first pulled from Y to X and then filtered. Since there is a huge amount of data in Y, this causes a dramatic decrease in performance.

When looking into some of the solutions, we found out about the DRIVING_SITE hint. This hint forces the query to be executed on the defined server and only return the result from that.

So consider the following query that returns the ename from a small X table and a huge Y table:

SELECT ename
  from empX x
     , empY@y y
 where x.deptno = y.deptno;

This will first pull all data from Y into X and then checks which rows comply with the WHERE clause.

Adding the DRIVING_SITE hint will change this:

SELECT /*+DRIVING_SITE(y)*/
       ename
  from empX x
     , empY@y y
 where x.deptno = y.deptno;

Now the much smaller amount of data from X will be used to filter the results in Y, before returning them.