Informatica – PowerCenter – Performance Tuning

Bottlenecks overview :
  • Target
  • Source
  • Mapping
  • Session
  • System
Using Thread statistics :

By default one reader thread,one transformation thread and one writer thread used to process a session.

The thread with highest busy percentage identifies the bottleneck in the session.

Busy time : percentage of runtime the thread is by according to the following formula :
(run time – idle time)/run time * 100

If one transformation requires more processing time than the others,consider adding a pass-through partition point to that transformation.

Target bottleneck :

Cause :
Small checkpoint intervals,small database network packet sizes,or problems during heavy loading operations

Identifying :

  • Check the writer thread statistics
  • Write to flat file target
Eliminating :
  • Optimize the query by DB admin
  • Increase database network packet size
  • Configure index and key constraints
Source bottlenecks :

Cause :
Insufficient query or small database network packet sizes

Identifying :

  • Check the reader thread statistics
If source is relational source,check following methods
  • Using Filter transformation next to SQ transformation
  • Using a read test mapping – remove all transformations and connect SQ trans to target flat file
  • Using a Database query – directly execute on database and check the execution time and time taken for first row return
Eliminating :
  • If source is flat file,set the number of bytes the integration service reads per line
If source is relational table
  • Optimize the query by DB admin
  • Increase database network packet size
  • Configure index and key constraints
  • Use optimizers hint,if there is a long delay between execution time and time taken for first row return in database query
Mapping bottlenecks :

Identifying :

  • Check transformation thread statistics
  • Analyze performance counters.High errorrows and rowsinlookupcache counters indicate a mapping bottleneck
  • Add a filter transformation before target and set flag as false.if it runs same as original then mapping is the bottleneck
Eliminating :
  • Optimize transformation settings in mapping
Session bottlenecks :

Cause :
Small cache size,low buffer memory and small commit intervals

Identifying :
Analyze performance details,which display information about each transformation,such as the no of input ,output and error rows

Eliminating :
Optimize the session

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s