RE: rules for database link usage

From: Matt <mvshelton_at_chartermi.net>
Date: Mon, 17 Feb 2014 11:13:25 -0500
Message-ID: <00b701cf2bfb$2fb88040$8f2980c0$_at_net>



I agree with the possible performance problem of views with database links. I have encountered this situation multiple time and one possible solution is the materialized view other times it's a tuning exercise. To decide on the question do I create a view over a database link or a materialized really depends on your requirements. For me I may do either based on the requirements of the application such things as uptime, maintenance, and performance .  

If I do create a database link to a remote object I always use views with private database links and put these objects in a separate schema to control security.  

Thanks, Matt

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Uzzell, Stephan
Sent: Monday, February 17, 2014 11:05 AM To: 'curtisbl_at_gmail.com'; backseatdba_at_gmail.com; oracle-l_at_freelists.org Cc: Uzzell, Stephan
Subject: RE: rules for database link usage  

Bobby,  

Could you say a bit more about the view issue (or point me towards some documentation)? One of the many fires we're fighting is a performance issue where a DB link is involved. We have not yet directly implicated the DB Link in the performance problem, but.  

Thanks,

stephan  

Stephan Uzzell  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobby Curtis
Sent: Monday, 17 February, 2014 10:56
To: backseatdba_at_gmail.com; oracle-l_at_freelists.org Subject: RE: rules for database link usage  

Jeff,  

This is a slippery slope. Database Links can be good; however, I've seen the wrong use of them where they cause a problems. Example, if you query a view on the remote side through a dblink, your wait times may go up because you have wait for the view to compile and then return the result set. This extends your connection and retrieval times.  

If you have to use a database link, I would use materialized views on the remote side to keep the data readily available. This way you are not compiling on the fly and waiting for the result set to be returned.  

Ideally, I try to keep the data as close as possible to where the execution is going to happen.  

Just my 2cents.  

Bobby  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeff C
Sent: Monday, February 17, 2014 10:48 AM To: oracle-l_at_freelists.org
Subject: rules for database link usage  

What are you rules for using database links in a production application? We currently have an application that needs data from another database and I am torn between having them just use a database link or copying the data to their database using various methods.

I am leaning towards duplicating the data because I prefer to not have this database dependent on the other or one causing havoc on the other. But I guess if that didn't matter what in general are your rules?  

Jeff


No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4335 / Virus Database: 3705/7096 - Release Date: 02/15/14

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 17 2014 - 17:13:25 CET

Original text of this message