Oracle database link performance cum security issue [message #663223] |
Fri, 26 May 2017 02:04 |
|
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 #663235 is a reply to message #663229] |
Fri, 26 May 2017 06:56 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
arpankumarmisra wrote on Fri, 26 May 2017 03:27One 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).
|
|
|
|
|