Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Slowness of DBLinks

Posted on 2004-11-11
Medium Priority
Last Modified: 2009-06-25
DBLinks have the reputation of being slow and therefore suitable only for occasional use, not for regular transaction processing.

However, I would not like to base the decision on whether to use or not use DBLinks purely on general reputation.  What would be ideal would be to have concrete 10046 Oracle Extended Trace Data detailing for some particular query or set of queries exactly what percentage of response time is taken up by 'Message From DBLink' or anything DBLink related.  (I am referring to the Resource Profiles outlined in Cary Milsap's book Oracle Performance.)  

Does anyone out there have any such Extended Trace Data producing any information at all about the slowness of DBLinks?  Does anyone have any hard data of any kind regarding DBLink performance?

And if hard data of any kind is lacking, does anyone have any based-on-experience guidance on when to use and when not to use DBLinks?

Question by:CliffWirt
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 500 total points
ID: 12561023
The dblink speed surely is constrained by the hardware link and how hard the servers at each end are working.  If you throw dedicated
hardware at the bottleneck you can improve performance.

If speed is an issue then use replication.

Assisted Solution

boriskalavsky earned 500 total points
ID: 12561119
From my experience DBLinks work for OLAP queries against large datasets with a smaller result sets.
LVL 48

Accepted Solution

schwertner earned 1000 total points
ID: 12563193
It depends how you use the DBLINK.
If you use it only for SELECTs it should work with the speed of the physical network connection between the
instances with some overhead.
If you use DBLINK for transaction processing in distributed environment Oracle automatically uses 2PC (Two Phase Commit) protokol. In this special case the overhead is big and of course DBLINK works "slowly". But this comes from the Two Phase Commit protocol.

Author Comment

ID: 12750595
I used Schwertners answer as part of the basis of a report I gave on the use of dblinks in a certain application.

The report was supposed to decide whether dblinks were having an excessive performance impact on the application's queries.  Since the queries that used dblinks in the application were all SELECTS, I thought it wasn't extremely likely that the dblinks were having much of a performance impact.  If those queries were transactions that had to use the 2PC, that would have been a different story.

To see if the actual performance impact conformed to my expectations, I did a level 8 extended trace of the application (see Cary Milsap's book, OPTIMIZING ORACLE PERFORMANCE) and ran it through Milsap's profiler.  The trace showed that the dblink-related waits took very little time, the times ranging from 0.188 seconds (for a query whose total response time was 1.092 seconds) to just 0.0046 seconds (for a query whose total response time I was unable to determine because of a collection error I had made during the trace).  

So the trace confirmed my expectation that, because all that is involved in SELECTS by way of dblinks is network time plus a little overhead, the performance impact of dblinks on the application's queries would be small.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question