OBJECTIVE • The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks .
General steps • The first step in performance tuning is to identify • • •
the performance bottleneck . Analyze the cause for the bottleneck. Eliminate it. Check the session performance and repeat the above steps until it is satisfactory.
Performance bottlenecks can occur in
• • • • •
Target Source Mapping session System
Target bottleneck
Causes: • Small check point intervals , small database
network packet size , problems during heavy loading operations .
If Relational table as target: • Check by populating the records to a flat file. If Flat file as target: • check by populating the records to a flatfile in local power center server.
Optimizing methods for relational target: 2.Increasing Checkpoint Intervals 2. Use Bulk Loading . 3. Increasing Database Network Packet Size --for sybase & sql server increase from 8k-16k --for oracle increase in tnsnames.ora and listener.ora 4. Optimizing Oracle Target Databases --by checking the storage clause, space allocation, and rollback segments in appropriate table spaces.
Methods to identify Source bottlenecks Flat file source: Using “Line Sequential Buffer Length setting” set the no. of bytes the power center reads per line.( default:1024 bytes).
Relational source: • Use a filter transformation in the mapping to
• •
measure the time it takes to read source data. Use a Read test session. Database query—execute the read query present in sql override directly in database itself.
Optimizing methods for relational source:
• Optimize the query. • Create tempdb as an in-memory database to • •
allocate sufficient memory–for Sybase or Microsoft SQL Server database Use conditional filters (i.e., using filter condition in sql override of source qualifier itself). Increase database network packet size --larger packets of data to cross the network at one time .
Methods to identify Mapping bottlenecks:
• Use a filter transformation before the target table • •
and set the condition to false so that no data is loaded into the target. Multiple lookups can slow down the performance. Transformation errors impact session performance so check the transformation errors in session log file.
Mapping optimization
• Configure Single-Pass Reading --allows you to
populate multiple targets with one source qualifier.
• Avoid unnecessary data type conversions. • Reduce the number of transformations in the mapping.
• Minimize the amount of data moved by deleting unnecessary links between transformations.
• Limiting the number of connected input/output or output ports reduces the amount of data the transformations store in the data cache.
Single pass reading Source System 1
Source Qualifier1
Exp1
Aggregator1
Target
Source System 2
Source Qualifier2
Exp2
Aggregator2
Target
Single Passing
Source System 1
Source System 2
Source Qualifier
Aggregator1
Target
Aggregator2
Target
Exp
Look up Optimization: • Implement caching the lookup table • Reduce the Number of Cached Rows by using •
lookup sql override. Always use equal ( = ) sign first in lookup condition then use other signs such as <, >, <= ,>= , != etc.,
• Use index in the lookup table.
Filter Optimization: • filter rows in sql override in source qualifier •
transformation itself. move the Filter transformation as close to the source qualifier. avoid using complex expressions in filter condition.
• • Use a Filter or Router transformation to drop rejected rows from an Update Strategy transformation
.
Aggregator Transformation: • often slow performance because they must group •
data before processing it. It need additional memory to hold intermediate group results.
Optimizing Methods: • Use simple columns i.e number instead of strings • •
and dates for group by clause. Use sorted input which decreases the use of aggregate caches when changed rows < target rows then Use “incremental aggregation “ which present in the session properties.
Joiner:
• need additional space at run time to hold •
intermediate results. uses data cache to hold the master table rows and an index cache to hold the join columns from the master table.
Optimizing joiner transformation:
• Ensure sufficient memory to hold the data cache • • •
and the index cache . Use smaller table as a master table. Normal joins are faster than outer joins. Use database joins for homogenous sources
Optimizing Sequence generator: • Create single seq.generator transformation and
•
use it for multiple pipeline in a single mapping instead of using different sequence generator for each pipe line. configure the Number of Cached Values property approx.,>1000 but not too small.
Optimize expressions: • Remove expressions one-by-one to isolate the slow expressions.
Steps to optimize: • • • •
Factoring Out Common Logic Minimizing Aggregate Function Calls Choosing DECODE versus LOOKUP Using Operators Instead of Functions
Evaluating Expressions:
• • • • •
If you are not sure which expressions slow performance ,then Time the session with the original expressions. Copy the mapping and replace half of the complex expressions with a constant. Run and time the edited session. Make another copy of the mapping and replace the other half of the complex expressions with a constant. Run and time the edited session.
Session optimizing • • • • •
Increase the number of partitions. Reduce errors tracing. Increasing the Cache Sizes . Increasing the Commit Interval . Remove staging areas.