Home » SQL & PL/SQL » SQL & PL/SQL » Oracle database link performance cum security issue (Oracle 10G R2)
Oracle database link performance cum security issue [message #663223] Fri, 26 May 2017 02:04 Go to next message
arpankumarmisra
Messages: 4
Registered: May 2017
Junior Member
Hi everyone!
Just joined today..

I have a problem that I want an opinion on. We have a java application running through Glassfish application server and connecting to an Oracle RDBMS. We are using the Oracle database link extensively, both, in our java application SQLs and also in our Oracle procedures, triggers and functions (PL/SQLs), to connect to other remote Oracle databases within the same hard box.

It has been decided that we will migrate the RDBMS from Oracle 10G to Oracle 11G soon. We have been advised by our migration implementation partner, that Oracle database links are not the best option due to performance and security issues and we have to endeavour to rewrite those codes without using database links.

As the instances of these database link SQLs are extensive, we are at a loss to both find a good alternative as also the reasons behind the claim made by our implementation partner.

My point is, if Oracle Corporation persists with database links in release 12C, then why is it not a good practice to use them in SQL/ PL-SQL to select, insert, delete and update.

1) Does using a database link hamper overall performance?
2) Is there a good alternative to a database link?

Thanks a lot for your views in advance..
Re: Oracle database link performance cum security issue [message #663224 is a reply to message #663223] Fri, 26 May 2017 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1) Yes.
2) Never use database link if you have not a very good reason to do so.
Why the objects you access using database links are not in the current database?

[Updated on: Fri, 26 May 2017 02:10]

Report message to a moderator

Re: Oracle database link performance cum security issue [message #663225 is a reply to message #663224] Fri, 26 May 2017 02:24 Go to previous messageGo to next message
arpankumarmisra
Messages: 4
Registered: May 2017
Junior Member
1) Can you please elaborate why there are performance issues? It'll help me!
2) The data that we fetch using database links are owned by different applications using their own databases. We aren't the owner. We also use Oracle functions and procedures across the database links which are written by their developers, the logic of which will be Hebrew to us and may change from time to time. So, the practical option of getting things to work was through database links.

Thanks for your prompt response
Re: Oracle database link performance cum security issue [message #663226 is a reply to message #663225] Fri, 26 May 2017 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1)
a) Accessing something remotely is obviously more expensive than locally
b) There are not as many connections to the remote database than there are accesses to it; so connections to the remote database are shared among several sessions.
c) Oracle optimizer can't do its best job as it can't optimize, rewrite queries, merge accesses between local and remote objects, so you have sub-optimized execution plans

2) Can't they be in the same database? If you have no possibility to change that then the whole topic is irrelevant.
The only thing I see you can do in 12c (if it is a possibility for you) is to create a multi-tenant database with each application in a plugged database in it but I don't know if the optimizer is aware that the database links point to the same multi-tenant database (maybe John can answer this).

Re: Oracle database link performance cum security issue [message #663227 is a reply to message #663226] Fri, 26 May 2017 02:40 Go to previous messageGo to next message
arpankumarmisra
Messages: 4
Registered: May 2017
Junior Member
1) Thanks!!!
2) They actually can't be in the same database.. And we are not migrating to 12C. So...

Anyways thanks a lot again..
Re: Oracle database link performance cum security issue [message #663229 is a reply to message #663227] Fri, 26 May 2017 03:27 Go to previous messageGo to next message
arpankumarmisra
Messages: 4
Registered: May 2017
Junior Member
One more thing..

Is there a good alternative to database links? May not be Oracle based! Like java application web services for e.g.?

Am not sure though whether using this alternative will lessen the overall performance of the application as that will require many lines of application code to replace a single instance of database link.

Anybody has any idea on how to go about making these changes?

Thanks in advance..
Re: Oracle database link performance cum security issue [message #663234 is a reply to message #663223] Fri, 26 May 2017 06:52 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
>It has been decided that we will migrate the RDBMS from Oracle 10G to Oracle 11G soon.
Upgrading from one unsupported version to another unsupported version, is an exercise in futility, IMO.

How many different ORACLE_SID (database) comprise this application?
Re: Oracle database link performance cum security issue [message #663235 is a reply to message #663229] Fri, 26 May 2017 06:56 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
arpankumarmisra wrote on Fri, 26 May 2017 03:27
One more thing..

Is there a good alternative to database links? May not be Oracle based! Like java application web services for e.g.?

Am not sure though whether using this alternative will lessen the overall performance of the application as that will require many lines of application code to replace a single instance of database link.

Anybody has any idea on how to go about making these changes?

Thanks in advance..
1) as for alternatives, I've never worked with Java, but it is my understanding that the java program can open its own connections to multiple databases simultaneously. Of course, ANYTHING you do to eliminate the use of db links will require massive rework of your application.

2) as for the contractor's advice and recommendation, I'd take with a large grain of salt and a jaundiced eye. Many contractors simply don't know what they are talking about, and I'm afraid I've observed (on the various Oracle forums) that it is getting worse. Example one - I once had a third-party product rep tell me that yes, their product would work with an Oracle database but they didn't recommend it because "Oracle cannot support more than five simultaneous connections." Yes, he said that with a straight face. His product didn't make the cut. And on my current position, the vendor of the third-party app on one of my databases is always asking questions and making suggestions that show they really (really, really!) don't understand Oracle. In addition to the potential incompetence factor, could it be that they have a vested interest in making suggestions that could potentially lead to more billable hours for them?

I really think the concern about the use of db links is overblown. I don't see where "security" is any more of a concern with a db link than any other method. There's nothing magic or strange about a db link. It is simply one database opening a client connection to another database -- exactly like any other application would open a client connection. As for "performance", yes there is a theoretical - and sometimes real - performance hit, but this can only be determined by testing your actual application. But you've already done that, as your application is already in use. If you find the performance acceptable, then you have no issue. Upgrading the db is not going to change the fundamentals of that. And if you think about it, any supposed "alternative" still has to do exactly what a db link does -- open a client connection to another database, and pull data from that database across the network.

BTW, what you are doing (moving from an older version of oracle to a newer) is called "upgrade", not "migrate". "Migrate" suggests moving from one platform to another, like migrating from one OS to another or from one rdbms (oracle) to another (MSSQL).
Re: Oracle database link performance cum security issue [message #663740 is a reply to message #663235] Thu, 15 June 2017 11:11 Go to previous messageGo to next message
Caffeine+
Messages: 14
Registered: February 2017
Junior Member
Another method to avoid recoding the app is to use a loop-back DB link. I used this to consolidate a handful of small applications where it didn't make sense to keep in their own databases. We had no resources to change the code. So we just imported the small schemas and replaced the DB link so that it pointed right back to our database. No other change was necessary. In your case, you might have to resolve multilingual issues but the DBMS has features just for that purpose. We support Cantonese, Hungarian, German, Polish, Spanish and English on the same database.

HTH.
Re: Oracle database link performance cum security issue [message #663741 is a reply to message #663223] Thu, 15 June 2017 12:20 Go to previous message
joy_division
Messages: 4802
Registered: February 2005
Location: East Coast USA
Senior Member
How many tables are involved in all the code that are remote? If it's very few, why not use Materialized Views?
As Ed has said though, all of the code involving database links would have to be rewritten with any other method other than staying status quo.
Previous Topic: Locked object in gv$locked_object without transaction in gv$transaction
Next Topic: stored proc execution based on selected value from a column
Goto Forum:
  


Current Time: Wed Dec 13 23:02:51 CST 2017

Total time taken to generate the page: 0.01755 seconds