To start, you can take few stack traces from the session that are running slow.
So if you see calls where write send the data packet to DB and read is where we wait from for an acknowledgement.
For example, read calls (avg 0.013 seconds) take much longer than write calls (0.0001 seconds). Usually anything under 0.0001 seconds is good anything over it will show performance issues.
You would need to check with your DBAs and network team on this, you can share the analysis with them.
1 of 1 people found this helpful
You switched from Solaris to Windows and from Oracle to SQL-server (repositories I presume).
I think you also changed the underlying hardware as well but PowerCenter is seen as the bottleneck.
The big issue with performance is it's a complex thing.
I have seen well performing and ill performing PowerCenter on windows for various reasons.
You need to take a look at the whole infrastructure if you really want to find out where your bottleneck is.
Especially since you didn't do an in-place upgrade of Informatica.
To give you an example of my experience with ill performing PowerCenter on Windows.
I have been working in an environment where PowerCenter itself was blamed but eventually it turned out the storage was the issue since the IT department had configured back-ups to be stored in tier-1 and all the rest in tier-2 and tier-3 (tier-1 is the fastest) so caching etc was way to slow in PowerCenter so reader and writer were idle for large percentages.
The session stats you have posted may not be all that helpful.
If I simply glance at the time difference between Windows and Solaris (Windows- 2k seconds vs. Solaris - 4600+ seconds) I would say that Windows is performing MUCH BETTER!
But of course I think the row count must be different here if you say that Windows is performing worse than Solaris.
Please upload the stats for a session run with a similar number of rows and also the stack traces Syed mentioned.
At this point all that can be surmised from the stats in the session log is that the reader and DTM threads are taking more time and the writer is taking less.
To further isolate the stacks are really needed here.