CliffWirt
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.