DWBIConcepts is launching APEAR – an Automated Performance Evaluation and Reporting tool for Informatica. As the name suggests, this tool will help you tune the performance of Informatica sessions fully automatically. Now don't waste your precious time any longer trying to figure out how to speed up your Informatica sessions.

Overview of APEAR

APEAR is a UNIX bash shell based tool that is capable of analyzing Informatica PowerCenter session logs and generate reports containing suggestions to improve session performance. The tool is FREE and tested with Informatica PowerCenter 8.5/8.6.

What does this tool do?

APEAR analyzes the informatica session log files and calculates and derives crucial performance insights from the log. Next APEAR uses this insight to suggest solutions or improvement points to augment the session performance and displays the suggestions in a nifty looking report. All these are done with zero manual intervention.

How can APEAR automate Informatica performance tuning?

APEAR uses a lot of calculation and heuristics (e.g. statistical deviations from normal distribution to identify query performance issue, etc.) and utilizes the generally accepted best practice guideline to suggest solutions.

In which platform APEAR runs?

APEAR needs Linux/Unix platform with bash shell

How does an APEAR report look like?

Please see a sample APEAR report below

AUTOMATED PERFORMANCE REPORTING FOR INFORMATICA MAPPING: m_3NF_GEN_DATA_LOAD_MAP_FINAL
 

PARTITION POINT                        | STAGE            |BUSY%      |COMMENT
---------------------------------------|------------------|-----------|---------------------
[SQ_DWH_DATA_LOAD_MAP_TEMP]            |read              |4.599      |
[SQ_DWH_DATA_LOAD_MAP_TEMP]            |transformation    |94.31      |Possible Bottle-neck
[DWH_DATA_LOAD_MAP_SNDWHTEMP1,         |write             |11.18      |
 
LOOKUP         |DTM BUFF|DTM BLCK| DCACHE | ICACHE |ROW(S)    |QTIME |CTIME |TTIME |TP% |DATA MEM |INDX MEM |FLAG
---------------|--------|--------|--------|--------|----------|------|------|------|----|---------|---------|----
LKP_DWH_DUMMY1 |609824  |65536   |14917632|7459200 |30        |0     |0     |0     |-   |         |         |
LKP_DWH_DUMMY2 |609824  |65536   |14917632|7459200 |285175    |1     |1     |1     |2.85|         |         |
LKP_DWH_DUMMY3 |609824  |65536   |14917632|7459200 |9528      |0     |0     |0     |-   |         |         |
LKP_DWH_DUMMY4 |609824  |65536   |14917632|7459200 |4853279   |87    |33    |87    |.55 |310624256|85828400 |PQ
LKP_SUB_DUMMY5 |609824  |65536   |14917632|7459200 |31449562  |410   |231   |410   |.76 |505167872|897080800|P
LKP_DWH_DUMMY6 |609824  |65536   |14917632|7458000 |792928    |3     |2     |3     |2.64|19062784 |35604800 |P
LKP_DWH_DUMMY7 |609824  |65536   |14917632|7459200 |129239    |2     |1     |2     |.64 |18915328 |3698800  |DQ
LKP_DWH_DUMMY8 |609824  |65536   |14917632|7458000 |1629778   |6     |3     |6     |2.71|39165952 |73185200 |P
ULKP_DWH_DUMMY9|609824  |65536   |14917632|7459200 |258       |0     |0     |0     |-   |         |         |
LKP_DWH_DUMMY10|609824  |65536   |14917632|7458000 |1950112   |10    |6     |10    |1.95|46858240 |87564400 |P
---------------|--------|--------|--------|--------|----------|------|------|------|----|---------|---------|----

TP% : Relative through-put of the lookup (1 mil rows/sec equals TP%=10)
FLAG: U- Unsorted Input, Q- Query may need tuning, P- Disk paging for Data and Index, D- Paging for data
 
SUGGESTION(s)

1.  LKP_DWH_DUMMY4: Not enough memory, data and index cache may page out.
[4853279] rows(of avg. len 64.00) needs 310624256(data) + 85828400(index) bytes of memory.
But it has 14917632 and 7459200 bytes respectively. You can increase memory from Properties
2.  LKP_SUB_DUMMY5: Not enough memory, data and index cache may page out.
[31449562] rows(of avg. len 160.62) needs 5051678720(data) + 897080800(index) bytes of memory.
But it has 14917632 and 7459200 bytes respectively. You can increase memory from Properties
3.  LKP_DWH_DUMMY7: Not enough memory, data cache may page-out to disk.
[129239] rows (avg. row len 146.35) needs 18915328 bytes of memory but it has 14917632 bytes
You can increase memory from Properties settings
4.  Possible bottleneck at partition point [SQ_DWH_DATA_LOAD_MAP_TEMP]
5.  The lookup sql query of [LKP_DWH_DUMMY4] may need some tuning
6.  The lookup sql query of [LKP_DWH_DUMMY7] may need some tuning  

How can I download APEAR?

APEAR source code is freely downloadable from this website. Since this is basically a shell script, the code can be directly executed in UNIX bash.

VersionDescriptionLink
APEAR1.0Initial ReleaseDownload

How to run/use APEAR?

APEAR can be executed by following the below simple steps:

  1. Download the latest version of APEAR from the download link given above
  2. Place the downloaded zip file in your UNIX/Linux server, preferably in the same server where Informatica is installed
  3. Unzip the zip file using "unzip -j " command. This will create a .bsh file in the working directory
    $>unzip -j APEAR.zip
  4. Execute the .bsh file from the command prompt and pass the full-path and name of the session log file for the session that you want to tune. See below
    $>chmod +x APEAR.bsh
    $>./APEAR.bsh /SessLogs/informatica-session-log-file-name.txt
    
    If your session log is in binary formatted, you may use the '-c' command line switch to convert the session log.
    ./APEAR.bsh -c/informatica/server/bin/infacmd.sh /SessLogs/informatica-session-log-file-name.log.bin

Where Can I report bug?

Please mail me "admin [at] dwbiconcepts [dot] com" to report any bug that you encounter.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • How to Tune Performance of Informatica Lookup Transformation

    To me, look-up is the single most important (and difficult) transformation that we need to consider while tuning performance of Informatica jobs. The choice and use of correct type of Look-Up can dramatically vary the session performance in...

  • Pushdown Optimization In Informatica

    Pushdown Optimization which is a new concept in Informatica PowerCentre, allows developers to balance data transformation load among servers. This article describes pushdown techniques.

  • How to get Folders and Mapping names from Informatica Metadata Query

    We can use OPB_MAPPING and OPB_SUBJECT tables residing under informatica Repository to obtain information about all the mappings under each Informatica Folder. Following SQL query shows you how to do it.

  • Using Informatica Stored Procedure Transformation

    Stored Procedure Transformation - as the name suggests is used to execute stored procedures through Informatica ETL. It can also be used to call functions to return calculated values. The Stored Procedures that are to be executed should be...

  • Informatica Join Vs Database Join

    In this yet another "DWBI Concepts' Original article", we test the performance of Informatica PowerCentre 8.5 Joiner transformation versus Oracle 10g database join. This article gives a crucial insight to application developer in order to take...

  • How to Tune Performance of Informatica Joiner Transformation

    Joiner transformation allows you to join two heterogeneous sources in the Informatica mapping. You can use this transformation to perform INNER and OUTER joins between two input streams. For performance reasons, I recommend you ONLY use JOINER...

  • Informatica Metadata Tables - Overview and Tutorial

    Informatica PowerCentre stores all the information about mapping, session, transformation, workflow etc. in a set of database tables called metadata tables. While these tables are used internally by Informatica, one can get useful information by...

  • Using Informatica Normalizer Transformation

    Normalizer transformation is a native transformation in Informatica that can ease many complex data transformation requirements. Learn how to effectively use normalizer in this tutorial.

  • Challenges of Informatica Partitioning with Sequencing

    In the previous article, we showed how surrogate keys can be generated without using Sequence Generator transformation. However, if Informatica partitioning is implemented for such cases, then since each partition pipeline will call the lookup...

  • Informatica Performance Tuning - Complete Guide

    This article is a comprehensive guide to the techniques and methodologies available for tuning the performance of Informatica PowerCentre ETL tool. It's a one stop performance tuning manual for Informatica.