Someone asked me today “Why is this session running so long?” hmmm seems like time to take up informatica session performance.
Well, a very common question that everyone working in Informatica would have come across– Why? Yet every session has its own parameters to look for and to consider like the process, hardware, and of-course the way Informatica mapping is coded and configured.
Today, I am going to start a discussion on how a developer can begin his analysis in search of an answer for this “WHY” with the available resources.
Key to Informatica Session Performance Read – Read , Cache only needed data, and optimize mapping transformation
We will discuss about reading and caching the data. Considering a production environment – the only accessible part for you is session log. Let’s start with that.
Pull the session long and quickly go to the end of load, you can find the overall session statistics down there (as shown in the snapshot above)
- Check the busy percentage, there may always be an opportunity to tune the component if it is less busy or idle for longer time.
- Source – If your source is 90-100% busy and following transformations are more idle, smell a problem with the source
- Required data only (Rows & Columns)
- Effective joining strategy
- Cache – Watch out the caching components and check how long it took to cache the data. This check will help you to identify if caching is causing the delay in session execution. If so you can work on finding alternatives or effectively cache the required data only to bring down the time of caching.
INFO10/22/2012 4:19:10 AMnode01_xxxxxxxxxxxMAPPINGCMN_1792The data cache size that would hold  rows in the lookup table for [LKP_xxxxxxxx], in memory, is  bytes
INFO 10/22/2012 4:13:11 AM node01_xxx LKPDP_4:TRANSF_1_1 DBG_21079 DBG_21079 Creating Lookup Cache : (Mon Oct 22 04:13:11 2012)
INFO 10/22/2012 4:13:12 AM node01_xxx LKPDP_4:READER_1_1 BLKR_16008 Reader run completed.
INFO 10/22/2012 4:13:12 AM node01_xxx LKPDP_4:TRANSF_1_1 DBG_21682 Lookup table row count : 72336
INFO 10/22/2012 4:13:12 AM node01_xxx LKPDP_4:TRANSF_1_1 DBG_21297 Lookup cache row count : 42336
INFO 10/22/2012 4:13:12 AM node01_xxx LKPDP_4:TRANSF_1_1 DBG_21294 DBG_21294 Lookup cache creation completed : (Mon Oct 22 04:43:52 2012)
More data in Cache (Case 1 )
- See if you really require to cache the data; and cache can be replaced with any alternatives
- In case of no alternatives, see if you are optimizing the cache by only selecting the required data from the table using an override in lookup
- One can always consider configuring lookups to share the cache and building cache in advance if the lookup table data is independent
More time consuming (Case 2)
- Check the cache build starting and closing times. The time consumed should be appropriate and in line with your performance expectations from your system
- It took around half an hour to create cache for less than 50K rows and it’s a fish. Identify the problems to be fixed with that lookup