Informatica Power Center - Performance Tunning Details

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 next performance 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 we tune all the bottlenecks, we 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.

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.

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

How to know where the bottleneck is:

Using Thread Statistics:

There are 3 types of threads:

1. Reader
2. Writer
3. Transformation

Note: The more busy a thread is causing higher bottleneck.

Use Writer Thread Statistics to find out target bottleneck:

Solution:

• Configure Indexes and Key constraints on target Database
• Use Bulk option
• Increase Commit Intervals to reduce the number of checkpoints
• Increase the connection group size to decrease chances of Deadlocks
• keep a dedicated storage for Target Flat Files in the server/native directory
• Increase the DB network packet size