Performance Tuning

The goal of performance tuning is to optimize session performance by eliminating

performance bottlenecks. The most common performance bottleneck occurs when the
Informatica Server writes to a target database.

Once you determine the location of a performance bottleneck, you can eliminate the
bottleneck by following these guidelines:

• Eliminate source and target database bottlenecks. Have the database

administrator optimize database performance by optimizing the query, increasing
the database network packet size, or configuring index and key constraints.
• Eliminate mapping bottlenecks. Fine tune the pipeline logic and transformation
settings and options in mappings to eliminate mapping bottlenecks.
• Eliminate session bottlenecks. You can optimize the session strategy and use
performance details to help tune session configuration.
• Eliminate system bottlenecks. Have the system administrator analyze
information from system monitoring tools and improve CPU and network
performance.

If you tune all the bottlenecks above, 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.

Performance bottlenecks can occur in the source and target databases, the mapping, the
session, and the system.

You can identify performance bottlenecks by running test sessions, viewing performance
details, and using system monitoring tools.

1. Identifying Target Bottlenecks

You can identify target bottlenecks by configuring the session to write to a flat file target.
If the session performance increases significantly when you write to a flat file, you have a
target bottleneck.

If your session already writes to a flat file target, you probably do not have a target
bottleneck.

Causes for a target bottleneck may include small check point intervals, small database
network packet size, or problems during heavy loading operations.

1
GE Confidential
2. Identifying Source Bottlenecks

Performance bottlenecks can occur when the Informatica Server reads from a source
database. If your session reads from a flat file source, you probably do not have a source
bottleneck. You can improve session performance by setting the number of bytes the
Informatica Server reads per line if you read from a flat file source.

If the session reads from relational source, you can use a filter transformation, a read test
mapping, or a database query to identify source bottlenecks.

3. Identifying Mapping Bottlenecks

You can identify mapping bottlenecks by using a Filter transformation in the mapping.
You can also identify mapping bottlenecks by using performance details. High errorrows
and rowsinlookupcache counters indicate a mapping bottleneck.

If you determine that you do not have a source bottleneck, you can add a Filter
transformation in the mapping before each target definition. Set the filter condition to
false so that no data is loaded into the target tables. If the time it takes to run the new
session is the same as the original session, you have a mapping bottleneck.

Multiple lookups can slow down the session. You might improve session performance by
locating the largest lookup tables and tuning those lookup expressions.

Transformation errors impact session performance. If a session has large numbers in any
of the Transformation_errorrows counters, you might improve performance by
eliminating the errors.

4. Identifying a Session Bottleneck

You can identify a session bottleneck by using the performance details. The Informatica
Server creates performance details when you enable Collect Performance Data in the
Performance settings on the Properties tab of the session properties.

Performance details display information about each Source Qualifier, target definition,
and individual transformation. All transformations have some basic counters that indicate
the number of input rows, output rows, and error rows.

Any value other than zero in the readfromdisk and writetodisk counters for Aggregator,
Joiner, or Rank transformations indicate a session bottleneck. Low
BufferInput_efficiency and BufferOutput_efficiency counter values also indicate a
session bottleneck.

Small cache size, low buffer memory, and small commit intervals can cause session
bottlenecks.

2
GE Confidential
Aggregator, Rank, and Joiner Readfromdisk and Writetodisk Counters

If a session contains Aggregator, Rank, or Joiner transformations, examine each

Transformation_readfromdisk and Transformation_writetodisk counter.

If these counters display any number other than zero, you can improve session
performance by increasing the index and data cache sizes.

To view the session performance details while the session runs, right-click the session in
the Workflow Monitor and choose Properties. Click the Properties tab in the details
dialog box.

Source and Target BufferInput_efficiency and BufferOutput_efficiency Counters

If the BufferInput_efficiency and the BufferOutput_efficiency counters are low for all
sources and targets, increasing the session DTM buffer pool size may improve
performance.

Under certain circumstances, tuning the buffer block size may also improve session
performance.

5. Identifying a System Bottleneck

You can identify system bottlenecks by using system tools to monitor CPU usage,
memory usage, and paging.

The Informatica Server uses system resources to process transformation, session

execution, and reading and writing data. The Informatica Server also uses system
memory for other data such as aggregate, joiner, rank, and cached lookup tables. You can
use system performance monitoring tools to monitor the amount of system resources the
Informatica Server uses and identify system bottlenecks.

On Windows, you can use system tools in the Task Manager or Administrative Tools.

On Windows, you can view the Performance and Processes tab in the Task Manager (use
Ctrl-Alt-Del and choose Task Manager). The Performance tab in the Task Manager
provides a quick look at CPU usage and total memory used. You can view more detailed
performance information by using the Performance Monitor on Windows (use Start-
Programs-Administrative Tools and choose Performance Monitor).

1. Percent processor time 2. Pages/second 3. Physical disks percent time

4. Physical disks queue length 5. Server total bytes per second

3
GE Confidential
Optimizing the Target Database

If your session writes to a flat file target, you can optimize session performance by
writing to a flat file target that is local to the Informatica Server. If your session writes to
a relational target, consider performing the following tasks to increase performance:

• Drop indexes and key constraints.

If you decide to drop and rebuild indexes and key constraints on a regular basis, you can
create pre- and post-load stored procedures to perform these operations each time you run
the session.

Note: To optimize performance, use constraint-based loading only if necessary.

• Increase checkpoint intervals.

The Informatica Server performance slows each time it waits for the database to perform
a checkpoint. To increase performance, consider increasing the database checkpoint
interval.

• Use bulk loading.

When bulk loading, the Informatica Server bypasses the database log, which speeds
performance. Without writing to the database log, however, the target database cannot
perform rollback. As a result, you may not be able to perform recovery. Therefore, you
must weigh the importance of improved session performance against the ability to
recover an incomplete session.

• Use external loading.

If your target database runs on Oracle, you can use the Oracle SQL*Loader utility to bulk
load target files. When you load data to an Oracle database using a pipeline with multiple
partitions, you can increase performance if you create the Oracle target table with the
same number of partitions you use for the pipeline.

• Increase database network packet size.

You can increase the network packet size in the Informatica Workflow Manager to reduce
target bottleneck. For Oracle, increase the network packet size in tnsnames.ora and
listener.ora. If you increase the network packet size in the Informatica Server
configuration, you also need to configure the database server network memory to accept
larger packet sizes.

• Optimize Oracle target databases.

4
GE Confidential
If your target database is Oracle, you can optimize the target database by checking the
storage clause, space allocation, and rollback segments.

Optimizing the Source Database

If your session reads from a flat file source, you can improve session performance by
setting the number of bytes the Informatica Server reads per line. By default, the
Informatica Server reads 1024 bytes per line. If each line in the source file is less than the
default setting, you can decrease the Line Sequential Buffer Length setting in the session
properties.

If your session reads from a relational source, review the following suggestions for
improving performance:

• Optimize the query.

• Create tempdb as in-memory database.
• Use conditional filters.
• Increase database network packet size.
• Connect to Oracle databases using IPC protocol.

5
GE Confidential