Re: "DB Link are Inherently Slow" -- True or False?

How close in proximity are the databases?  The farther they are apart the more problems you will have with db_links as far as times and disconnections.
 

I have used db_links for large queries but normally when the connection is good and the hops/distance is trivial.
 

How many hops is the connection taking.
 

We found an issue recently with how things were routing and that cleared up our problem
 

You could also be running into other network traffic items that slow things down. I do bulk items across db links when the network is slow (if you know when that is)
 

I use Materialized Views to do alot of my heavy lifting since I have to transfer alot of data from alot of tables.  I have my dbms jobs set to run every 15 minutes.  Works great. If the network goes down for any reason I don't have to worry about things getting out of sync.  The dbms_job will try again and if for some reason it isn't resolved and the job breaks.. I get an email message.  Once the network issue has been corrected, I can fix the MV normally by reruning the job or every blue moon do a refresh. 
 

You might also look into Oracle Streams.. I have no experience with this.
 

my 2.5 cents worth.
 

K

• On Tue, 7/15/08, Jared Still <jkstill_at_gmail.com> wrote:

From: Jared Still <jkstill_at_gmail.com>
Subject: Re: "DB Link are Inherently Slow" -- True or False? To: david_at_david-aldridge.com
Cc: "Oracle List" <oracle-l_at_freelists.org> Date: Tuesday, July 15, 2008, 12:44 PM

On Tue, Jul 15, 2008 at 6:23 AM, David Aldridge <david_at_david-aldridge.com> wrote:

I am being persistently told by a DBA that DB links are inherently slow and not suited to bulk transfer of data.
 

Does anyone have any experiences to share on the sort of practical MBytes/sec throughput we ought to be getting on a 10GBit network between two databases having no intervening firewalls, routers, or other potentially performance limiting network components? We're seeing 2MBytes/sec using data pump import in network mode with parallelsim of 10 (ie. sourcing data through a db link from another db on a different host) :(

Hi David,

The referred to slowness is probably due to the sometimes sub-optimal execution plans that are generated when some of the data is obtained via dblink in multi-table queries.

You may want to google for this, it is an issue that has appeared in the past, and there are some workarounds - hints (driving_site is one) and use of views and some other hints.

The problem was often that a FTS was done on the remote table(s) when that would not have been the case if the table were local.

You may want to check on any improvements for this in 10g/11g, I haven't looked at it myself in quite awhile.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist



      
--
http://www.freelists.org/webpage/oracle-l