Performance Tuning

Identifying Performance Bottleneck

Taking Corrective Actions
 Identifying Performance Bottleneck

The first step in performance tuning is to identify the performance

bottleneck. Performance bottlenecks can occur in the source and target
databases, the mapping, the session, and the system. Generally, you
should look for performance bottlenecks in the following order :

• Target
• Source
• Mapping
• Session
• System

You can identify performance bottlenecks by running test sessions,

viewing performance details, and using system monitoring tools.
 Target Optimization
• Typically your targets would be :
• A) Database
• B) Flat File

• Database : If your target is a database, then configure session

properties to write to a flat file. If the session
performance increases significantly then the target
database is the bottleneck.
• Flat File : If your session already writes to a flat file target, you probably
do not have a target bottleneck. You can optimize session performance by
writing to a flat file target local to the Informatica Server
 Database Target Bottleneck
• Typical Causes :

• A) Small check point intervals

• B) Small database network packet size
• C) Problems during heavy loading
operations
 Database Target Optimization
• Perform following tasks to improve performance :

• A) Drop indexes and key constraints

• B) Increase checkpoint intervals
• C) Use bulk loading
• D) Use external loading
• E) Increase database network packet size
• F) Optimize Oracle target databases
 Database Target Optimization
• Drop indexes and key constraints :
• When you define key constraints or indexes in target tables, you slow the loading of
data to those tables. To improve performance, drop indexes and key constraints
before running your session. You can rebuild those indexes and key constraints after
the session completes. (Use pre-session and post-session property for this)

• 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. When you increase the database checkpoint interval, you
increase the likelihood that the database performs checkpoints as necessary, when

the size of the database log file reaches its limit

 Database Target Optimization
• Use bulk loading :
You can use bulk loading to improve the performance of a session that inserts a large
amount of data to a Sybase, Oracle, or Microsoft SQL Server database. Configure
bulk loading on the Properties settings of the Targets tab.

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.
 Database Target Optimization
• Use external loading :
A) DB2 : If you have a DB2 EE or DB2 EEE target database, you can use the DB2
EE or DB2 EEE external loaders to bulk load target files.
B) Teradata : If you have a Teradata target database, you can use the Teradata
external loader utility to bulk load target files.
C) Oracle : 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.
D) Sybase : If your target database runs on Sybase IQ, you can use the Sybase IQ
external loader utility to bulk load target files
 Database Target Optimization
• Increase database network packet size :

You can increase the network packet size in the Informatica Workflow Manager to
reduce target bottleneck. 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.
 Database Target Optimization
Optimize Oracle target databases :
Take the help of Oracle DBA.
 Identifying Source Bottlenecks
Sources can be :

A) Flat File : 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.
B) Relational 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.
 Identifying Source Bottlenecks
Relational Source :
A) Using a Filter Transformation : Add a filter transformation in the mapping after each
source qualifier. Set the filter condition to false so
that no data is processed past the filter
transformation. If the time it takes to run the new
session remains about the same, then you have a
source bottleneck.
B) Using a Read Test Session :
1) Make a copy of the original mapping.
2) In the copied mapping, keep only the sources,
source qualifiers, and any custom joins or queries.
3) Remove all transformations.
4) Connect the source qualifiers to a file target.

Use the read test mapping in a test session. If the test session
performance is similar to the original session, you have a source
bottleneck.
C) Using a Database Query : Check execution path of read query against database using
utilities like explain plan to identify source bottleneck.
 Optimizing Source Database
Relational Source :
A) Optimize the query : Check the execution path of the query (using explain plan) and
take necessary optimization steps like creation of index and giving
optimizing hints.
B) Use conditional filters : Instead of using filters on non-indexed table columns (on database
side), use Informatica’s filter transformation. You can test your session
with both the database filter and the PowerCenter/PowerMart filter to
determine which method improves performance.
C) Increase database network packet size :
You can improve the performance of a source database by increasing
the
network packet size, allowing larger packets of data to cross the
network
at one time. To do this you must complete the following tasks:
• Increase the database server network packet size.
• Change the packet size in the Workflow Manager database
connection to reflect the database server packet size.
Identifying Mapping Bottlenecks
After identifying that the bottleneck is neither source nor target, introduce a filter just
before each of the targets and set the filter property to false to ensure that no rows
are loaded. If the mapping is still taking almost the same time as earlier then the
mapping is the bottleneck for performance.

You can also perform the following tasks to optimize the mapping:
• Configure single-pass reading.
• Optimize datatype conversions.
• Eliminate transformation errors.
• Optimize transformations.
• Optimize expressions.
 Optimizing the Mapping
Configuring Single-Pass Reading :
Single-pass reading allows you to populate multiple targets with one source qualifier.

Figure 22-1. Single-Pass Reading

 Optimizing the Mapping
• Optimizing Datatype Conversions :
Forcing the Informatica Server to make unnecessary datatype conversions slows
performance. For example, if your mapping moves data from an Integer column to a
Decimal column, then back to an Integer column, the unnecessary datatype
conversion slows performance. Where possible, eliminate unnecessary datatype
conversions from mappings.
 Optimizing the Mapping
• Optimizing Aggregate Transformation
:
1) Use sorted input (this requires data in sorted manner
from input side)
2) Use incremental aggregation (if the changes
captured from source changes less
than half the target)
 Optimizing the Mapping
• Optimizing Lookup Transformations :
1) Cache the lookup table (preferably if it needs less than 300MB space).
2) Indexing the lookup table.
3) Shared cache : You can share the lookup cache between multiple transformations
(if unnamed within same mapping and named across mappings)
4) Persistent cache : If you want to save and reuse the cache files, you can configure
the transformation to use a persistent cache. Use this feature when you know the
lookup table does not change between session runs.

• Optimizing Filter/Router Transformations :

1) If possible eliminate filter transformation by giving source qualifier filter.
2) If rejected rows are not required then update strategy should be preceded with
filter/router.
 Optimizing the Mapping
• Optimizing Joiner Transformations :
1) Use smaller table as master table.
2) Normal joins are faster than outer joins.

• Optimizing Filter/Router Transformations :

1) If possible eliminate filter transformation by giving source qualifier filter.
2) If rejected rows are not required then update strategy should be
preceded with
filter/router.
 Optimizing the Session
1) Partitioning
2) Increase DTM Buffer Pool Size (this should be the last option)
3) If you are manipulating unusually large rows of data, you can increase the buffer block
size to improve performance. If the session mapping contains a large number of
sources or targets, you might need to decrease the buffer block size.
4) Increase the cache size
5) Increase the commit interval
 Optimizing the System
1) Improve network speed
2) Use multiple PowerCenter Servers