OraFAQ Forum: SQL & PL/SQL » dblink

Home » SQL & PL/SQL » SQL & PL/SQL » dblink - stored proc

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dblink - stored proc [message #626437]

Mon, 27 October 2014 16:36

lijjumathew
Messages: 2
Registered: October 2014

Junior Member

Hi
I have two database A and B and we have a db link.
I have a stored proc in database b say SP1 which accesses tables in database B and gets some values.

1st Approach : I have a scenario where I have to call the stored proc SP1 from database A and get the value returned from SP1 and do some calculations in database A.
I just want to know how the proc gets executed. Does the SP1 gets executed in database B and gets the value ?

2nd Approach : Say I write the same logic that SP1 does in database A accessing the underlying tables from database B using the dblink and do the calculations.
Is this efficient performance wise

Thanks
Matt

Report message to a moderator

Re: dblink - stored proc [message #626438 is a reply to message #626437]

Mon, 27 October 2014 16:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: dblink - stored proc [message #626439 is a reply to message #626437]

Mon, 27 October 2014 22:41

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Stored procedures execute on the database in which they are compiled. At least this is my understanding of them. As a general rule then, the fewer database hops you have to make to pull data into your procedure, the better. If you have a procedure on database A and it makes lots of calls across a database link to database B, then it will very likely result in many database jumps to get data. It will likely be more efficient to execute the procedure from the same database in which the data resides. It is also somewhat more secure due to the HARD BINDING that will happen when the procedure and row sources are all local to each other as compared to the SOFT BINDING that will occur due to the database jumps.

In truth though, it may depend a great deal on what you do with the rows once you get them. It sounds like your PL/SQL procedure may be doing a lot of data work which is not what PL/SQL was meant for. Thus a review of how the procedure works would be in order.

Good luck. Kevin

Report message to a moderator

Re: dblink - stored proc [message #626542 is a reply to message #626439]

Tue, 28 October 2014 15:08

Bill B
Messages: 1971
Registered: December 2004

Senior Member

if it is accessing a large table over the dlink, you might have the procedure on the first database and use the DRIVING_SITE hint to run the code on the other database. It will make a major difference in speed. I had a simple update that was accessing table on a different database and I put in the hint and the update went from 2 hours to 3 seconds.

Report message to a moderator

Previous Topic:	Loading Multi line columns using external table
Next Topic:	replace string with hyphens

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 17:10:17 CDT 2024