Slowness of DBLinks

Posted on 2004-11-11
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
    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.
    LVL 4

    Assisted Solution

    From my experience DBLinks work for OLAP queries against large datasets with a smaller result sets.
    LVL 47

    Accepted Solution

    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

    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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: 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…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to recover a database from a user managed backup

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now