Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Sunday, 7 July 2013

Improve Performance In Informatica

By on 11:15:00

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.


Post a Comment

Blog Archive


1z0-482 Dumps (2) 1Z0-525 Dumps (1) BI Apps Installation (1) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (47) Informatica Online Training (1) informatica scenarios questions (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) Normalization in Oracle (1) OBIA (1) OBIA (3) OBIA Installation (33) OBIEE (2) OBIEE 10G (8) OBIEE 11G (138) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) Power BI (5) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)