Alternatives for Dblink in oracle

dblinkdistributed-databasesoracle

we have an SSIS job that inserts data into a table in server A from data in table which is located in some other server B.

For this,we are making use of dblinks to connect to other database . But, this is causing lot of performance issues and job is failing because it is taking lot of time to execute the query.

So, i would like to know , what are the alternatives for Dblinks. The one thing i got is , using Materialistic views .

BUt i am not sure , how that works ? Please suggest..

Best Answer

The alternative to using database links would be to create different connections in your SSIS package for each database and to use those connections to pull data from the remote database to the SSIS server and then push that data into the target database. It is highly unlikely, however, that this would lead to performance improvements. Most likely, it would mean that your data is spending much more time going across additional network links.

Materialized views would not be a replacement for database links. Materialized views would use database links. They would replace some or all of your SSIS package.

Why do you believe that the database links are the source of your performance problems? Is your SSIS package causing Oracle to do set-based operations to pull data from the remote server to the local server? Or is SSIS doing row-based operations where individual rows are being extracted from the remote database and loaded into the local database. Row-based operations are inherently much slower than set-based operations particularly when data is being sent over the network. SSIS generally makes it very easy to build pretty-looking row-by-row ETL processes. That combination seems far more likely to explain your performance problems than database links.

How does "taking a long time" translate to "failing"? If an SSIS job fails, that implies that an error is thrown. What is that error? Or are you saying that the job is technically successful but that it takes so long that it violates your SLA and, thus, the business considers the job to be a failure?

If you are actually doing set-based operations in your SSIS package and your package isn't throwing an error, it just takes too long to run, then you would probably need to start looking at tuning the individual set-based operations that your code is performing by looking at things like query plans in the database. It is possible that you're missing indexes or to ensure that joins happen on the correct server or that your load needs to be refactored to ensure that data is brought from the remote server only once rather than many times. We'd need to understand more about exactly what statements are taking a long time, however, to be able to help you tune them.