A Performance Tuning
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View A Performance Tuning as PDF for free.
More details
- Words: 391
- Pages: 21
Performance Tuning* Check ‘Collect Performance data’ option in the session properties of a session in Server Manager
Performance Tuning
Performance Tuning Source Qualifier
Target
Evaluation
Buffer Input efficiency
BufferOutput efficiency
Buffer Input efficiency
Buffer Output efficiency
High
Low
High
Low
Source database slow, eliminate read bottleneck
Low
Low/High
Low
High
Target database slow, eliminate write bottleneck
Low
High
High
Low
DTM slow, optimize session or mapping
Efficiency Percentage High= 80-100% Low= 0-20%
Performance Tuning The counters help identify Read/Write/DTM bottlenecks Caching problems Transformation errors Shared memory allocation problems
To avoid read bottleneck Reduce the number of records processed by filtering the source Index the source Replace default query with an optimized query in SQL override of Source Qualifier
Performance Tuning* To avoid write bottleneck Utilize SQL loader facility of the database Drop indexes before load and rebuild after the load Increase the database block size
To avoid DTM bottleneck •
Reduce the amount of detail in the session logs
Performance Tuning
Performance Tuning* To avoid DTM bottleneck •
Run parallel sessions in Concurrent batches
•
Use incremental aggregation for mappings that use aggregation
Performance Tuning
Performance Tuning* To avoid DTM bottleneck Optimize mapping Optimize session
For cache problems •
Increase index and data cache
Performance Tuning
Click
Performance Tuning* For shared memory allocation problems •
Increase the shared memory size
Performance Tuning
Performance Tuning To optimize sessions •
Increase shared memory size
•
Increase buffer block size for very large row sizes
•
Consider the commit interval. More commits=slower performance
•
Disable decimal arithmetic if possible
Performance Tuning
Performance Tuning To optimize mapping Utilize single pass reads, use SQL override Place filters, aggregators as close to source as possible Trim spaces before comparing strings Operators are faster than functions,i.e.,|| vs Concat Cache lookup table columns
Performance Tuning
Optimizing Transformations
Performance Tuning Filter Transformation Use the Filter transformation early in the mapping. Use the Source Qualifier transformation to filter.
Performance Tuning Joiner Transformation Perform joins in a database when possible. Join sorted data when possible. For an unsorted Joiner transformation, master---> fewer rows. For a sorted Joiner transformation, master source --->fewer duplicate key values
Performance Tuning Lookup Transformation Cache small lookup tables(<300 MB) Use a persistent lookup cache for static lookup tables. Override the ORDER BY statement for cached lookups
Performance Tuning Stored Procedure Transformation Reduce the row count. Create an expression
Performance Tuning
Performance Tuning Source Qualifier
Target
Evaluation
Buffer Input efficiency
BufferOutput efficiency
Buffer Input efficiency
Buffer Output efficiency
High
Low
High
Low
Source database slow, eliminate read bottleneck
Low
Low/High
Low
High
Target database slow, eliminate write bottleneck
Low
High
High
Low
DTM slow, optimize session or mapping
Efficiency Percentage High= 80-100% Low= 0-20%
Performance Tuning The counters help identify Read/Write/DTM bottlenecks Caching problems Transformation errors Shared memory allocation problems
To avoid read bottleneck Reduce the number of records processed by filtering the source Index the source Replace default query with an optimized query in SQL override of Source Qualifier
Performance Tuning* To avoid write bottleneck Utilize SQL loader facility of the database Drop indexes before load and rebuild after the load Increase the database block size
To avoid DTM bottleneck •
Reduce the amount of detail in the session logs
Performance Tuning
Performance Tuning* To avoid DTM bottleneck •
Run parallel sessions in Concurrent batches
•
Use incremental aggregation for mappings that use aggregation
Performance Tuning
Performance Tuning* To avoid DTM bottleneck Optimize mapping Optimize session
For cache problems •
Increase index and data cache
Performance Tuning
Click
Performance Tuning* For shared memory allocation problems •
Increase the shared memory size
Performance Tuning
Performance Tuning To optimize sessions •
Increase shared memory size
•
Increase buffer block size for very large row sizes
•
Consider the commit interval. More commits=slower performance
•
Disable decimal arithmetic if possible
Performance Tuning
Performance Tuning To optimize mapping Utilize single pass reads, use SQL override Place filters, aggregators as close to source as possible Trim spaces before comparing strings Operators are faster than functions,i.e.,|| vs Concat Cache lookup table columns
Performance Tuning
Optimizing Transformations
Performance Tuning Filter Transformation Use the Filter transformation early in the mapping. Use the Source Qualifier transformation to filter.
Performance Tuning Joiner Transformation Perform joins in a database when possible. Join sorted data when possible. For an unsorted Joiner transformation, master---> fewer rows. For a sorted Joiner transformation, master source --->fewer duplicate key values
Performance Tuning Lookup Transformation Cache small lookup tables(<300 MB) Use a persistent lookup cache for static lookup tables. Override the ORDER BY statement for cached lookups
Performance Tuning Stored Procedure Transformation Reduce the row count. Create an expression
Related Documents
A Performance Tuning
November 2019 1
Performance Tuning
November 2019 24
Performance Tuning
June 2020 20
Performance Tuning
November 2019 33
Performance Tuning
October 2019 30