I've seen some discussions from time to time here about performance tuning. First thing to check is always the data flow of course. But if the mapping's design is ok, then there's time to look for other issues. One thing affecting performance heavily is disk usage. If there's not enough memory assigned to the tasks, PowerCenter uses disk cache - and this is sloooow.
To help handling this I've created a simple, 3-step tool that creates a report listing tasks in all sessions that were using disk cache during given time. The tool is free, open-source, and is available here:
https://community.informatica.com/solutions/1785
Here's what you need to do:
1. Execute "python PowerCenterCacheFileWatcher.py" on IS and give the amount of minutes you want cache to be monitored. Regardless from the amount entered, a report is created every minute with the already observed files. Once it's finished (or anytime during monitoring) you can check the PowerCenterCacheFilesList.txt file to get the list of cache files and their sizes. Only files created after PowerCenterCacheFileWatcher.py has been started and with size greater than configured treshold are being reported. This is to avoid chaos.
2. If you're tuning just one session, you can save the session log on local disk using PowerCenter Monitor. You'll get the .txt file. And you can skip this point.
But if you'd like to check everything that was run on IS for a given time, then after PowerCenterCacheFileWatcher.py is finished, download all session logs created during the monitoring period. To simplyfy this, dates are printed at the end of the run. When you copy Session Logs from IS, you'll have .bin files that need to be converted to .txt - and that's exactly what PowerCenterLogFilesConverter.py does. Execute "python PowerCenterLogFilesConverter.py" and all files found in sessLogSrcDirPath will be converted and stored as .txt files in sessLogTgtDirPath.
3. Executing "python PowerCenterLogFilesAnalyzer.py" will create PowerCenterCacheFilesReport.txt report file that will contain a list of all cache files and names of tasks that created them grouped by session names. This should allow editing session properties and assigning propoer amount of memory to all tasks to avoid disk use.
Currently Lookups, Aggregators and Sorters are being supported, yet the sorters are being treated in a bit different way: you just get the log message that gives precise info about required memory usage. Additional tasks (i.e. Rankers, Normalizers) can be added if needed.
As mentioned, the above can be used to tune any particular session as well as perform check on a whole IS to check if there are any Session using disks extensively impacting other tasks.
Hope this will serve you well.