Slowness of DBLinks

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?

CliffWirtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

earth man2Commented:
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.
0
boriskalavskyCommented:
From my experience DBLinks work for OLAP queries against large datasets with a smaller result sets.
0
schwertnerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CliffWirtAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.