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..


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.

  • WoW! Great answer. This would certainly help me in solving my issue .The query is not much complex it only has just have two join operations. Yes you guess is indeed correct, since the job is not being completed with in the stipulated time, we can considering it as job failure rather than being technically failed – Avinash Kothamasu May 23 '14 at 6:32

A materialized views is not a replacement for a database link. It would likely create a local materialized view using data pulled accross a database link. What you need to do is tune the query that you are using for your materialized view.

Run the query in sqlplus with timing and autotrace turned on. Look for cartesean joins, full table scans and sorts. There are probably some additional indexes or changes to indexes that you can make that will make the query cheaper to run. You want all of the filtering and sorting to happen on the source side of the link so that you are bringing accross as little data as possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.