Follow Us on Twitter

Better performance on database links

by Michel van Zoest on September 2, 2010 · 1 comment

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.

Better performance on database links, 4.5 out of 5 based on 4 ratings
Ratings:
VN:D [1.9.22_1171]
Rating: 4.5/5 (4 votes cast)

{ 0 comments… add one now }

Leave a Comment

 

{ 1 trackback }

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter