Re: "DB Link are Inherently Slow" -- True or False?
- From: kathy duret <katpopins21@xxxxxxxxx>
- To: Oracle List <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jul 2008 12:31:06 -0700 (PDT)
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@xxxxxxxxx> wrote:
From: Jared Still <jkstill@xxxxxxxxx>
Subject: Re: "DB Link are Inherently Slow" -- True or False?
To: david@xxxxxxxxxxxxxxxxxx
Cc: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
Date: Tuesday, July 15, 2008, 12:44 PM
On Tue, Jul 15, 2008 at 6:23 AM, David Aldridge <david@xxxxxxxxxxxxxxxxxx>
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
Other related posts:
