Wednesday, April 8, 2015

Oracle Tuning for linking local table with remote table over DBLink

When we are working with multiple applications on Oracle, there might be a need to link those applications together. One of the way to integrate them is though bank end DB Link. 

There is one technique I would like to introduce to help on the DBLink performance. This little tuning tip will help a lot when you join the tables from both side of the DBLink. 

When we join 2 or more tables from both end of the DBLink and the query is executed, the data will be executed with back-and-forth behaviour. This will cause the pain in your query performance. In case of the table on the remote is very large and the local table is smaller, the helping tips is using oracle hint /*+DRIVING_SITE (remote_table_alias)*/. Where remote_table_alias is the table alias on the remote side. 

I have come across the Oracle tuning technique that might help us over DBLink. 
There might be some scenario that we need to join local table with remote table over DBLink, the  driving_site hint might help us with the performance. Please take a look at the example below:

select /*+DRIVING_SITE(r)*/ 
   r.key, r.code,
    remote_table@dblink r
    tiny_local_table        l, 
     r.key = l.key;

The driving_site hint will operate the join and sorting on the remote site with this scenario.
However, this technique will not work with create table as select (CTAS) and with create materialized view syntax.