RE: database links and performance

From: Knight, Jon <jknight_at_concordefs.com>
Date: Tue, 8 Feb 2005 09:42:45 -0600
Message-ID: <17ECCBDCF27C544583F2CAD928F953260221FAE4@memex1.corp.cefs.int>

I can second Tom's comment that "bad things happen" when joining across links. Especially if the remote tables have much size. Other strategies are create a function on the remote DB to return just a small result set across the link (if applicable.) Replicate certain data to your local DB depending on needs/space.
We have also observed a performance hit (significant in some cases) even when both DBs are on the the same box. That said, links are a great thing. We use them all the time.

Jon Knight
Senior Database Analyst
2525 Horizon Lake Drive, Suite 120
Memphis, TN 38133
JKnight_at_concordefs.com

901.371.8000 - Phone
800.238.7675 - Phone
901.380.8336 - Fax
www.FirstData.com

First Data's merger with Concord creates "One Company" with enhanced choice, voice and innovation for all customers.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F

Sent:	Tuesday, February 08, 2005 8:49 AM
To:	'jdunn_at_sefas.com'; Oracle-L_at_freelists.org
Subject:	RE: database links and performance

John,

I use them in a couple of applications and generally, they work fine. A slow network is a slow network. Yes, it can hurt performance. If you think that your network cannot support your application, then find another way to do it.

A couple of tricks to consider:

If you need to join tables in the remote computer, consider creating a view in that computer and calling that view from your local machine. It will perform much better - basically, limit the joins from remote tables - bad things happen.

Good Luck.

Tom

-----Original Message-----

From: John Dunn [mailto:jdunn_at_sefas.com] Sent: Tuesday, February 08, 2005 9:40 AM To: Oracle-L_at_freelists.org
Subject: database links and performance

We are considering using database links in Oracle 9i but are concerned about performance.

Presumably the network can be a bottleneck when using database links.

Does anyone have any useful experiences with performance of database links?

--

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

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

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 08 2005 - 10:45:34 CST