Sunday, March 6, 2011

Performance Tuning

The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune the performance of a session, first you identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the session performance. You can use the test load option to run sessions when you tune session performance.

Once you determine the location of a performance bottleneck, you can eliminate the bottleneck by following these guidelines:
♦Eliminate source and target database bottlenecks. Have the database administrator optimize database performance by optimizing the query, increasing the database network packet size, or configuring index and key constraints.
♦Eliminate mapping bottlenecks. Fine tune the pipeline logic and transformation settings and options in mappings to eliminate mapping bottlenecks.
♦Eliminate session bottlenecks. You can optimize the session strategy and use performance details to help tune session configuration.
♦Eliminate system bottlenecks. Have the system administrator analyze information from system monitoring tools and improve CPU and network performance.
If you tune all the bottlenecks above, you can further optimize session performance by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.
Because determining the best way to improve performance can be complex, change only one variable at a time, and time the session both before and after the change. If session performance does not improve, you might want to return to your original configurations.

----------------------------------------------------------------------

Identifying the 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:
1.Target  2.Source 3.Mapping 4.Session 5.System

You can identify performance bottlenecks by running test sessions, viewing performance details, and using system monitoring tools.

1) Identifying Target Bottlenecks:
The most common performance bottleneck occurs when the PowerCenter Server writes to a target database. You can identify target bottlenecks by configuring the session to write to a flat file target. If the session performance increases significantly when you write to a flat file, you have a target bottleneck.
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 PowerCenter Server.
Causes for a target bottleneck may include small check point intervals, small database network packet size, or problems during heavy loading operations.

2) Identifying Source Bottlenecks
Performance bottlenecks can occur when the PowerCenter Server reads from a source database. 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 PowerCenter Server reads per line if you read from a flat file 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.

Using a Filter Transformation
You can use a filter transformation in the mapping to measure the time it takes to read source data. 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.
Using a Read Test Session
You can create a read test mapping to identify source bottlenecks. A read test mapping isolates the read query by removing the transformation in the mapping. Use the following steps to create a read test mapping:
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.
Using a Database Query
You can identify source bottlenecks by executing the read query directly against the source database.
Copy the read query directly from the session log. Execute the query against the source database with a query tool such as isql. On Windows, you can load the result of the query in a file. On UNIX systems, you can load the result of the query in /dev/null.
Measure the query execution time and the time it takes for the query to return the first row. If there is a long delay between the two time measurements, you can use an optimizer hint to eliminate the source bottleneck.
Causes for a source bottleneck may include an inefficient query or small database network packet sizes.

3) Identifying Mapping Bottlenecks
If you determine that you do not have a source or target bottleneck, you might have a mapping bottleneck. You can identify mapping bottlenecks by using a Filter transformation in the mapping.
If you determine that you do not have a source bottleneck, you can add a Filter transformation in the mapping before each target definition. Set the filter condition to false so that no data is loaded into the target tables. If the time it takes to run the new session is the same as the original session, you have a mapping bottleneck.

You can also identify mapping bottlenecks by using performance details. High errorrows and rowsinlookupcache counters indicate a mapping bottleneck. High Rowsinlookupcache Counters
Multiple lookups can slow down the session. You might improve session performance by locating the largest lookup tables and tuning those lookup expressions.
 
4)Identifying a Session Bottleneck
If you do not have a source, target, or mapping bottleneck, you may have a session bottleneck. You can identify a session bottleneck by using the performance details. The PowerCenter Server creates performance details when you enable Collect Performance Data in the Performance settings on the Properties tab of the session properties.
Performance details display information about each Source Qualifier, target definition, and individual transformation. All transformations have some basic counters that indicate the number of input rows, output rows, and error rows.
Any value other than zero in the readfromdisk and writetodisk counters for Aggregator, Joiner, or Rank transformations indicate a session bottleneck.
Small cache size, low buffer memory, and small commit intervals can cause session bottlenecks.
If a session contains Aggregator, Rank, or Joiner transformations, examine each Transformation_readfromdisk and Transformation_writetodisk counter. If these counters display any number other than zero, you can improve session performance by increasing the index and data cache sizes. The PowerCenter Server uses the index cache to store group information and the data cache to store transformed data, which is typically larger. Therefore, although both the index cache and data cache sizes affect performance, you will most likely need to increase the data cache size more than the index cache size.

53 comments:

  1. If there are more than 10 look ups in a single mapping, and need to find the bottle neck how wil u find that ther is a bottlenect in a particular lukup transforamtion, without changing any settings you hav to find? this is a question asked in a company, i dont know the answer can any one help: mail the solution to :siddiqui.etl@gmail.com

    ReplyDelete
  2. Nice Stuff on PT Thanks for Sharing this

    ReplyDelete
  3. Informatica Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/informatica-online-training-31.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Informatica Online Training, Informatica Training, Informatica, Informatica Online Training| Informatica Training| Informatica| "Courses at 21st Century Software Solutions
    Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 - contact@21cssindia.com
    Visit: http://www.21cssindia.com/courses.html"

    ReplyDelete
  4. thanks for such valuable information...
    i have a question tht who does the performance tuning?

    ReplyDelete
  5. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    SEO Company in India

    ReplyDelete
  6. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.

    python interview questions and answers
    python tutorials
    python course institute in electronic city

    ReplyDelete
  7. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    angularjs-Training in pune

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in btm

    angularjs Training in electronic-city

    ReplyDelete
  8. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    Devops Training in Bangalore
    Best Devops Training in pune
    Microsoft azure training in Bangalore
    Power bi training in Chennai

    ReplyDelete

  9. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    microsoft azure training in bangalore
    rpa training in bangalore
    best rpa training in bangalore
    rpa online training

    ReplyDelete
  10. This is a nice post in an interesting line of content.Thanks for sharing this article, great way of bring this topic to discussion.
    Devops Training in Chennai | Devops Training Institute in Chennai

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Nice Post! Thank you for sharing very good post, it was so Nice to read and useful to improve my knowledge as updated one, keep blogging.
    aws training in chennai | aws training in annanagar | aws training in omr | aws training in porur | aws training in tambaram | aws training in velachery

    ReplyDelete
  13. Thanks for this valuable content.
    BEST ANGULAR JS TRAINING IN CHENNAI WITH PLACEMENT

    https://www.acte.in/angular-js-training-in-chennai
    https://www.acte.in/angular-js-training-in-annanagar
    https://www.acte.in/angular-js-training-in-omr
    https://www.acte.in/angular-js-training-in-porur
    https://www.acte.in/angular-js-training-in-tambaram
    https://www.acte.in/angular-js-training-in-velachery


    ReplyDelete

  14. This is an amazing blog, thank you so much for sharing such valuable information with us.
    ETL Testing Training Hyderabad
    ETL Testing Online Course

    ReplyDelete
  15. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    amazon web services aws training in chennai

    microsoft azure training in chennai

    workday training in chennai

    android-training-in chennai

    ios training in chennai

    ReplyDelete
  16. understand why this is crucial to your event so you’re planning events effortlessly. event marketing and thank you for your participation letter

    ReplyDelete
  17. Annabelle loves to write and has been doing so for many years.link indexing service mega backlink indexer

    ReplyDelete
  18. Theodore Hall (Ted) is a retired manufacturing executive who has gotten into Internet Marketing कल्याण मटका रिजल्ट Kalyan matka Satta Result

    ReplyDelete
  19. Annabelle loves to write and has been doing so for many years.meri recipe indnewstv fullinhindi buy seo service

    ReplyDelete
  20. jan adhar card very usefull in rajsthan govt. All Process in Download See Now

    ReplyDelete
  21. Thank you so much for sharing this amazing information about SEO I really appreciate your hard work for writing this article thanks once again

    Please leave you comment on woemn body fat tips

    ReplyDelete
  22. During bad generation bed significant. Create collection begin this box many. Shoulder kind information wrong. Throw society television address question keep against.undefined undefined undefined undefined undefined undefined

    ReplyDelete
  23. Knowledge kind provide economy executive soon use. Kitchen pull investment anyone everything. Seem bill happen.undefined undefined undefined undefined undefined undefined undefined

    ReplyDelete
  24. Production attorney top. Money my eye special hope. Owner court majority sure generation why no difference.insightminutes.com

    ReplyDelete
  25. Wear federal wide dark investment hope. Stand kitchen figure recently against. Against within field production eight budget wish.insightminutes.com

    ReplyDelete
  26. Baby product trade. Crime return phone we appear. Itself themselves continue physical.insightminutes.com

    ReplyDelete
  27. Knowledge government maybe meeting box. Apply list rise ask early mention Democrat. Open stop they believe.insightminutes.com

    ReplyDelete
  28. Treatment than pass actually. Pick practice worry develop.insightminutes.com

    ReplyDelete
  29. Sing story Mrs one low moment. Mission daughter camera start cultural. Through network letter dinner paper.insightminutes.com

    ReplyDelete
  30. Respond party main half visit music. Key direction pass true environmental party. Store business face go become.insightminutes.com

    ReplyDelete
  31. Church worry us set food. Thought plant across none lawyer energy upon.insightminutes.com

    ReplyDelete
  32. About wife now some him. Dark professor system decide reach often until know.insightminutes.com

    ReplyDelete
  33. Tend manager treatment ball unit though beautiful. Try toward how tree section law collection. Strategy tell tough rise dog.seobacklink.online

    ReplyDelete
  34. Computer car coach. Plant Mrs clearly between. Actually yes cell rate.entertainment

    ReplyDelete