✍ Kashif M
Trends

Sunday, 7 July 2013


Improve Performance In Informatica


By on July 07, 2013


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.
  • Increase checkpoint intervals.
  • Use bulk loading.
  • Use external loading.
  • Turn off recovery.
  • Increase database network packet size.
  • Optimize Oracle target databases.


Dropping 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.
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.


Increasing 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.


Bulk Loading on Sybase and Microsoft SQL Server

You can use bulk loading to improve the performance of a session that inserts a large amount of data to a Sybase or Microsoft SQL Server database. Configure bulk loading on the Targets dialog box in the session properties.
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, the Informatica Server cannot perform recovery of the session. Therefore, you must weigh the importance of improved session performance against the ability to recover an incomplete session.
If you have indexes or key constraints on your target tables and you want to enable bulk loading, you must drop the indexes and constraints before running the session. After the session completes, you can rebuild them. If you decide to use bulk loading with the session on a regular basis, you can create pre- and post-load stored procedures to drop and rebuild indexes and key constraints.

For other databases, even if you configure the bulk loading option, Informatica Server ignores the commit interval mentioned and commits as needed.


External Loading on Teradata, Oracle, and Sybase IQ

You can use the External Loader session option to integrate external loading with a session.
If you have a Teradata target database, you can use the Teradata external loader utility to bulk load target files.
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 partitioned session, you can increase performance if you create the Oracle target table with the same number of partitions you use for the session.
If your target database runs on Sybase IQ, you can use the Sybase IQ external loader utility to bulk load target files. If your Sybase IQ database is local to the Informatica Server on your UNIX system, you can increase performance by loading data to target tables directly from named pipes. Use pmconfig to enable the SybaseIQLocaltoPMServer option. When you enable this option, the Informatica Server loads data directly from named pipes rather than writing to a flat file for the Sybase IQ external loader.


Increasing Database Network Packet Size

You can increase the network packet size in the Informatica Server Manager to reduce target bottleneck. For Sybase and Microsoft SQL Server, increase the network packet size to 8K - 16K. 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.


Optimizing Oracle Target Databases

If your target database is Oracle, you can optimize the target database by checking the storage clause, space allocation, and rollback segments.
When you write to an Oracle database, check the storage clause for database objects. Make sure that tables are using large initial and next values. The database should also store table and index data in separate tablespaces, preferably on different disks.
When you write to Oracle target databases, the database uses rollback segments during loads. Make sure that the database stores rollback segments in appropriate tablespaces, preferably on different disks. The rollback segments should also have appropriate storage clauses.
You can optimize the Oracle target database by tuning the Oracle redo log. The Oracle database uses the redo log to log loading operations. Make sure that redo log size and buffer size are optimal. You can view redo log properties in the init.ora file.
If your Oracle instance is local to the Informatica Server, you can optimize performance by using IPC protocol to connect to the Oracle database. You can set up Oracle database connection in listener.ora and tnsnames.ora.


Kashif
mkashu: Improve Performance In Informatica
Review : Kashif | Kashif
Update: July 07, 2013 | Rating: 4.5

Comment for "Improve Performance In Informatica"

0 comments

Post a Comment

Blog Archive