Informatica mapping taking 2.3 hours to run (tuning)

I am debugging a Informatica mapping which is currently taking 2.2 hours.

1) Sources are oracle tables and target flat file (fixed width)
2) 8 to 10 sources with individual SQL TRF
3) I found that out of all SQL TRF , 2 of them are taking almost 45 mins (20 million records) and 64 minutes (70 millions records) individually (reading source). However the SQL override is not very complex.

1. Put a filter after your SQ tr's and set the filter EXP to FALSE, run the mapping and note if it is still taking the same time. If yes then you have problem with sources.
2. Alternatively, open your session log and see the Thread Statistics for these pipelines (I am assuming that you have separate pipelines when you said separate SQ TRF), there you will have one thread each for SQ, TRANS and Target. look at the Busy % for each of them. If anyone of these is close to 100% that thread is a bottleneck in your case.

Consider doing below steps as the starting point if you find that your analysis was true for these 2 sources;

1. If nothings stopping you then consider putting all sources into one SQ and write a SQL override instead of different pipelines.
2. Index your sources properly if they are not already indexed.
3. Analyze / collect stats for your sources (especially if they are big ones) before pulling the data.
4. You can go for pipeline partitioning.

The next step will basically be decided based on your bottleneck analysis, but hope that this helps !

Thanks for the suggestions , however I am already scratching my head with that.

Okay to start up with , I am trying to tune these jobs (they are running since long time)

- I have tried the trick of using FILTER (false) after SQL , i was already sure looking at thread stat that the killer is TRAF not READER (see below) , so verified.
-to mee major killer is UNION TRF ( i dunno if i will take it into SQL level will it help or not)
-I have never used pipeline partitioning before , pls explain in details (as of now default partitioing in mapping : pass through is there )

I am also sending thread statistics this time for better visibility:

Thread Statistics:

Severity: INFO
Timestamp: 03/08/2011 14:34:08
Node: NO86PRE_BX631A
Thread: MANAGER
Process ID: 3936714
Message Code: PETL_24031
Message:
***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
Thread [READER_1_1_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_PRPOOL_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_PRPOOL_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_2_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_COMIS] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_2_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_COMIS] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_3_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_SALDOS] has completed.
Total Run Time = [6786.186834] secs
Total Idle Time = [4890.728162] secs
Busy Percentage = [27.931130]
Thread [TRANSF_1_3_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_SALDOS] has completed.
Total Run Time = [8232.065839] secs (137 mins)
Total Idle Time = [1076.295025] secs
Busy Percentage = [86.925578] (117 mins)
Thread work time breakdown:
agg_por_clave_balance: 17.202872 percent
srt_por_clave_balance: 8.481787 percent
exp_CompruebaCamposNulosCCPP: 1.515554 percent
agg_Cuentas: 13.267748 percent
agg_Suma_Saldos_Agrega_Auditoria: 3.589471 percent
un_Balance: 26.881149 percent (32 mins)
exp_asig_imps: 22.148365 percent
jnr_balance_clasifcvg: 6.913055 percent
Thread [READER_1_4_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_LR_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_4_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_LR_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_5_1] created for [the read stage] of partition point [sq_Shorcut_to_DWFD_LRPOOL_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_5_1] created for [the transformation stage] of partition point [sq_Shorcut_to_DWFD_LRPOOL_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_6_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_FUND] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_6_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_FUND] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_7_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_INTERES] has completed.
Total Run Time = [2665.335012] secs
Total Idle Time = [2132.402809] secs
Busy Percentage = [19.994942]
Thread [TRANSF_1_7_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_INTERES] has completed.
Total Run Time = [2517.713798] secs
Total Idle Time = [0.397988] secs
Busy Percentage = [99.984192]
Thread work time breakdown:
srt_por_clave_balance: 7.716786 percent
exp_CompruebaCamposNulosCCPP: 1.511535 percent
agg_Cuentas: 11.933174 percent
agg_Suma_Saldos_Agrega_Auditoria: 3.500398 percent
un_Balance: 48.687351 percent
exp_asig_imps: 20.763723 percent
jnr_balance_clasifcvg: 5.887033 percent
Thread [READER_1_8_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_FPOOL] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_8_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_FPOOL] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_9_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_PR_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_9_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_PR_UK] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_10_1] created for [the read stage] of partition point [sq_Shortcut_to_DWFD_COMIS_SERV] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_10_1] created for [the transformation stage] of partition point [sq_Shortcut_to_DWFD_COMIS_SERV] has completed. The total run time was insufficient for any meaningful statistics.
Thread [READER_1_11_1] created for [the read stage] of partition point [sq_Shortcut_to_f_EC_735_3746_ClasificacionCVG] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_11_1] created for [the transformation stage] of partition point [sq_Shortcut_to_f_EC_735_3746_ClasificacionCVG] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_13_1] created for [the transformation stage] of partition point [agg_Suma_Saldos_Agrega_Auditoria] has completed.
Total Run Time = [8641.855164] secs
Total Idle Time = [7817.946985] secs
Busy Percentage = [9.533927]
Thread work time breakdown:
agg_Suma_Saldos_Agrega_Auditoria: 100.000000 percent
Thread [TRANSF_1_14_1] created for [the transformation stage] of partition point [agg_por_clave_balance] has completed.
Total Run Time = [1376.525583] secs
Total Idle Time = [3.521741] secs
Busy Percentage = [99.744157]
Thread work time breakdown:
agg_por_clave_balance: 100.000000 percent
Thread [WRITER_1_*_1] created for [the write stage] of partition point [Shortcut_to_f_DW_610_3625_c_d_Interfaz_Manual, Shortcut_to_f_DW_610_3636_Avance_Detalle_Balance_P L_Diario, Shortcut_to_f_DW_610_3631_Detalle_Balance_PL] has completed.
Total Run Time = [8730.788304] secs
Total Idle Time = [7768.235188] secs
Busy Percentage = [11.024813]

Ashok..to ur fiest qs ..i have no idea..
2nd qs : we are fethcing these many records from the partition (daily job for that days partition and monthly for month partition)...hope its clear...
culprict is nnot reading..its the UNION..i dunno what to replace it with :(

Looking at the mapping structures and thread statistics you can try to check following things.
First Union is a non cache transformation so it can't be a bottleneck.
Your bottleneck is I guess is the Joiner.As it a cache transformation. Its waiting for all the rows to arrive from union then joining the data.
You can try following things
1) When selecting data from source order by column used for join in Joiner.Then use sorted input in joiner.
2) In joiner use the small table as cache table if not already doing it..

I hope you have already run the source qualifier sql to check how much time its taking to return all the rows.Tune the sql if there is a scope.

I am also concern with use of sorter that too based on 26 keys this will take huge amount of time to sort the data and nullify the performance gain in aggregrator.Try to push sorting to database engine .

As you are dealing with huge amount of data pls check if there is any scope to filter out data at any stage.

One last thing is please check if there is any scope to distribute the 10 sources in mapping into at least 2-3 mapping and then run them is parallel.

Hi Sandeep, just to add, even though Union is non cache, it is a blocking transformation and it is a bottleneck, as the union will wait for data from all the input pipelines to arrive.

I Just read this above:
stream 1--> there are 10 source(table) which are joined using UNION TRF and then next all these union data joined using JOINER with a very small file (100 records).

Are these tables in separate DB or same? If same then better union them in SQ, if on different DB then I would suggest have a pipeline to union the tables first and load data into a temp table or flat file then have a second pipeline to join this table/file with the smaller file and do the processing.

Also I would suggest not to use sorted input for aggregator, as sorter itself is an expensive transformation, instead you can increase the index and data cache size for aggregator.