Informatica - loading to target takes too much time

Question

I've created a simple mapping in informatica,where i add in the source qualifier the following statement: select * from srcTbl where field1='Y'. I'm loading only 3 fields to the target table, only 1 of them is from the source, the other to is output fields with a short text values.

the source and target are oracle tables, the target has index that is created by the 3 fields i'm loading.

I've changed the load mode to Normal instead of bulk because i can't drop the index.

it takes to much time, after an hour i just pressed abort.

the session log said that it's in the loading process.

what could be the reason for this process not to end quickly?

thanks

Answer 1

Look carefully in the session log, where the process is waiting. If it is in fetching the source records, then you have a source bottleneck, and you might need to tune your source query.

Or, you might have a target bottleneck, for which you can use partitioning, increase commit interval etc.

Also, the process may take long time building lookup cache, if you have huge data returning from a lookup query in your mapping.

There might also be network related issues in the connection between Informatica and Database server. The issue could be on the database end as well.

You have to check all the possibilities to pinpoint the issue. Go through the Informatica Performance Tuning guide, to know about all the ways you can optimize an Informatica process.