Performance Tuning
The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune session performance, first identify a performance bottleneck, eliminate it, and then identify the nextperformance bottleneck until you are satisfied with the session performance. You can use the test load option to run sessions when you tune session performance.
If you tune all the bottlenecks, you can further optimize session performance by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.
Because determining the best way to improve performance can be complex, change one variable at a time, and time the session both before and after the change. If session performance does not improve,
you might want to return to the original configuration.
Complete the following tasks to improve session performance:
1. Optimize the target. Enables the Integration Service to write to the targets efficiently.
2. Optimize the source. Enables the Integration Service to read source data efficiently.
3. Optimize the mapping. Enables the Integration Service to transform and move data efficiently.
4. Optimize the transformation. Enables the Integration Service to process transformations in a mapping
efficiently.
5. Optimize the session. Enables the Integration Service to run the session more quickly.
6. Optimize the grid deployments. Enables the Integration Service to run on a grid with optimal performance.
7. Optimize the PowerCenter components. Enables the Integration Service and Repository Service to function optimally.
8. Optimize the system. Enables PowerCenter service processes to run more quickly.
------------------------------------------------------------------------------------------------------------
Informatica Performance Tuning Guide Suggests following methods in their order to tackle the Bottlenecks
The first step in performance tuning is to identify performance bottlenecks. Performance bottlenecks can occur in the source and target databases, the mapping, the session, and the system. The strategy is to identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the performance.
Look for performance bottlenecks in the following order:
1. Target
2. Source
3. Mapping
4. Session
5. System
Use the following methods to identify performance bottlenecks:
- Run test sessions. You can configure a test session to read from a flat file source or to write to a flat file target to identify source and target bottlenecks.
- Analyze performance details. Analyze performance details, such as performance counters, to determin where session performance decreases.
- Analyze thread statistics. Analyze thread statistics to determine the optimal number of partition points.
- Monitor system performance. You can use system monitoring tools to view the percentage of CPU use, I/O waits, and paging to identify system bottlenecks. You can also use the Workflow Monitor to view systemresource usage.
Using Thread Statistics
You can use thread statistics in the session log to identify source, target, or transformation bottlenecks. By default,the Integration Service uses one reader thread, one transformation thread, and one writer thread to process a
session. The thread with the highest busy percentage identifies the bottleneck in the session.
The session log provides the following thread statistics:
¨ Run time. Amount of time the thread runs.
¨ Idle time. Amount of time the thread is idle. It includes the time the thread waits for other thread processing
within the application. Idle time includes the time the thread is blocked by the Integration Service, but not the
time the thread is blocked by the operating system.
¨ Busy time. Percentage of the run time the thread is by according to the following formula:
(run time - idle time) / run time X 100
You can ignore high busy percentages when the total run time is short, such as under 60 seconds. This does
not necessarily indicate a bottleneck.
¨ Thread work time. The percentage of time the Integration Service takes to process each transformation in a
thread. The session log shows the following information for the transformation thread work time:
Thread work time breakdown:
<transformation name>: <number> percent
<transformation name>: <number> percent
<transformation name>: <number> percent
If a transformation takes a small amount of time, the session log does not include it. If a thread does not have accurate statistics, because the session ran for a short period of time, the session log reports that the statistics
are not accurate.