Data Stage Designer Performance Tuning in Server jobs

1. In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call.
   
2. Implement the logic in a stage variable and then point the stage variable to the actual field.
   
3. Use Transforms rather than using routines.
   
4. While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000.
   
5. Adjust the array size setting. Try setting to 10, 100, or 1000.
   
6. If output rows are Inserts or Appends and not Updates, consider using a native bulk loader.
   
7. Eliminate unused columns.
   
8. Eliminate unused references.
   
9. Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job.
   
10. If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance.
    
11. Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR.
    
12. Avoid using 'like' operator in user defined queries in ODBC stages
    
13. Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs.
    
14. Tips while creating routines
15. Use variables in the routines.
16. Assign empty values to the variables before using them.
17. Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS.
18. For Example: Ans = ''
    If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356' End
    Ans = Ans
    The above logic can be implemented using
    ErrStr = ''Ans = ''
    If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr
    

Useful Guidelines in Designing Ascential Data Stage Server Jobs

1. Logically create the folders / subfolders on the file server so that the files can be placed and accessed from the relevant folders/subfolders.
   
2. Group the Jobs logically into various categories / Subcategories.
   
3. Comment the Jobs by using Annotation stage which tells the users, the functionality implemented in the job.
   
4. Give descriptions in the properties of the stages used so that others can identify the functionality implemented in it.
   
5. Name passive stages with the Table/File Names they access in it. 6. Name active stages to match their function.
   
6. Name links to express the direction and type of data flowing through them.
   
7. Use job parameters where ever it is required. This makes the process easy while moving into production.
   
8. While using ODBC stages remember to remove the derivations in the columns.
   
9. Use ODBC stage to access relational tables.
   
10. Move constraints from Transform stages to input stage WHERE clauses, to reduce the number of rows the job has to process.
    
11. Use the in-built functions present in Data Stage rather than creating a new routine for implementing the same logic as of in-built function.
    

Easy way to remove the column derivation

1. Open the transformer stage
2. Copy the columns from source(ODBC ) to target(Sequential File)
3. Delete the columns from source(ODBC)
4. Copy all the columns from target to the source.
5. Close the transformer.
6. Now we find that all the derivations are cleared from ODBC stage.

While using Reference Lookups

1. Compare the number of input rows with the number of rows in the reference table. If the reference table is smaller than the number of input rows, pre-load the reference table into a hash file and then reference the hash file.
   
2. Consider moving reference lookups to a join within the input stage. All columns used to join the tables should be indexed to maximize performance.
   
3. If the number of rows in a hashed file is small, consider Pre-loading the file into memory by checking the Pre-load file to memory checkbox in the Hash File stage.
   
4. Remove unused columns from transforms. This does not apply to columns in sequential files or output to hash files.
   
5. While mapping the input records with the Hash Look ups, remember that the fields getting mapped should be of same data type and of same length.
   
6. While loading the hash files, trim the data.

Oracle Certified Professional Exam Questions -Part I

Oracle Certified Professional

The Oracle Certified Professional recognizes achievement in mastering intermediate and advanced Oracle skills. Demonstrate proficiency and receive recognition in managing the implementation of Oracle technology while putting your career on the fast track.

Gain additional job opportunities - fill the Oracle skills gap in the marketplace Help your employers accelerate adoption of technology solutions and maximize ROI Increased opportunity to earn 21% more on average than your non certified counterparts

1. You are creating some tables in your database as part of the logical data model Which of the following constraints have an index associated with them that is generated automatically by Oracle?
   1. Unique
   2. Foreign-key
   3. Check
   4. NOT NULL
2. You have a table with three associated indexes, two triggers, two references to that table from other tables, and a view You issue the DROP TABLE CASCADE CONSTRAINTS statement Which of the following objects will still remain after the statement is issued?
   A The triggers B The indexes C The foreign keys in the other tables D The view
3. In order to set your SQL*Plus session so that your NLS_DATE_FORMAT information is altered in a specific way every time you log into Oracle, what method would be used?
   A Setting preferences in the appropriate menu option B Creating an appropriate LOGINSQL file C Issuing the ALTER USER statement D Issuing the ALTER TABLE statement

4. The EMP_SALARY table has two columns, EMP_USER and SALARY EMP_USER is set to be the same as the Oracle username To support user MARTHA, the salary administrator, you create a view with the following statement: CREATE VIEW EMP_SAL_VW AS SELECT EMP_USER, SALARY FROM EMP_SALARY WHERE EMP_USER <> 'MARTHA'; MARTHA is supposed to be able to view and update anyone in the company's salary except her own through this view Which of the following clauses do you need to add to your view creation statement in order to implement this functionality?

   1. WITH ADMIN OPTION
   2. WITH GRANT OPTION
   3. WITH SECURITY OPTION
   4. WITH CHECK OPTION
5. You are developing PL/SQL code to manipulate and store data in an Oracle table All of the following numeric datatypes in PL/SQL can be stored in an Oracle database, except one Which is it?
   1. CHAR
   2. RAW
   3. DATE
   4. INTEGER
6. You are performing some conversion operations in your PL/SQL programs To convert a date value into a text string, you would use which of the following conversion functions?
   1. CONVERT
   2. TO_CHAR
   3. TO_NUMBER
   4. TO_DATE
7. You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken Tests given in various locations throughout the country are stored in this table A student is not allowed to take a test for 30 days after failing it the first time, and there is a check in the application preventing the student from taking a test twice in 30 days at the same location Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location Which of the following SQL statements would be useful for identifying the students who have done so?
   1. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
   2. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
   3. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
   4. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
8. You create a view with the following statement: CREATE VIEW BASEBALL_TEAM_VW AS SELECT BJERSEY_NUM, BPOSITION, BNAME FROM BASEBALL_TEAM B WHERE BNAME = USER; What will happen when user JONES attempts to SELECT a listing for user SMITH?
   1. The SELECT will receive an error
   2. The SELECT will succeed
   3. The SELECT will receive NO ROWS SELECTED
   4. The SELECT will add data only to BASEBALL_TEAM
9. You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
   1. 51
   2. 95
   3. 30
   4. 75
10. What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
    1. 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
    2. A VARCHAR2 value
    3. An error; you can't do this with dates
    4. -36493 if the NLS_DATE_FORMAT is set to the default value
11. What is the purpose of the SUBSTR string function?
    1. To insert a capital letter for each new word in the string
    2. To return a specified substring from the string
    3. To return the number of characters in the string
    4. To substitute a non-null string for any null values returned
12. Evaluate this command: SELECT iisotope, gcalibration FROM chart_n i, gamma_calibrations g WHERE ienergy = genergy; What type of join is the command?
    1. Equijoin
    2. Nonequijoin
    3. Self-join
    4. The statement is not a join query
13. In a SELECT statement, which character is used to pass in a value at runtime?
    1. \
    2. %
    3. &
    4. _ (underscore)
14. Which single-row function could you use to return a specific portion of a character string?
    1. INSTR
    2. SUBSTR
    3. LPAD
    4. LEAST
15. What will the following statement return? SELECT LAST_NAME, FIRST_NAME, START_DATE FROM EMPLOYEES WHERE HIRE_date< Trunc(sysdate) 5;
    1. Employees hired in the past 5 years
    2. Employess hired in the past 5 days
    3. Employees hired more thatn 5 years ago
    4. Employees hired more than 5 days ago
16. Which function(s) accept arguments of any datatype? Select all that apply
    1. SUBSTR
    2. NVL
    3. ROUND
    4. DECODE
    5. SIGN
17. What will be returned from SIGN(ABS(NVL(-32,0)))?
    1. 1
    2. 32
    3. 1
    4. 0
    5. NULL
18. Which functions could you use to strip leading characters from a character string Select two
    1. LTRIM
    2. SUBSTR
    3. RTRIM
    4. INSTR
    5. MOD
19. what will the following query return? SELECT REPLACE(RTRIM('Anticipation','on'), 'ti','shun') from DUAL;
    1. Anticipashun
    2. Anshuncipashun
    3. Anshuncipashunon
    4. Anticipashunon
20. In oracle, what do trigonometric functions operate on?
    1. Degrees
    2. Radians
    3. Gradients
    4. The default is radians, but degrees or gradients can be specified
21. If it is 5 minutes past noon on 15 jan 2000, what will the following statement return? SELECT ROUND(SYSDATE) ROUND(SYSDATE,'Y') FROM DUAL;
    1. 155
    2. 15
    3. 0
    4. 16
22. Which statement about nested functions is most correct?
    1. Single-row nested functions can be nested in either single-row or group functions
    2. Group functions can be nested in other group functions
    3. Group functions cab be nested in single-row functions
    4. A, B and C
    5. A and B only
23. Why will the following query raise an exception? SELECT DEPT_NO, AVG(DISTINCT SALARY), COUNT(JO
24. JOB_COUNT FRIM EMP WHERE MGR LIKE 'J%' OR ABS(SALARY)>10 HAVING COUNT (JO
25. >5 ORDER BY 2 DESC;
    1. A HAVING clause cannot contain a group function
    2. The GROUP BY clause is missing
    3. Abs() is not an oracle function
    4. The query will not raise an exception
26. Why does the following SELECT statement fail? SELECT colorname Colour, MAX(cost) From itemdetail Where upper(colorname) like '%WHITE%' Group by colour Having count(*) > 20;
    1. A GROUP BY clause cannot contain a coloumn alias
    2. The condition COUNT (*) > 20 should be in the WHERE clause
    3. The GROUP BY clause must contain the group functions used in the SELECT list
    4. The HAVING Clause can only contain the group functions used in the SELECT list
27. What will the following query report? SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
    1. The number of employees in each department, including those without a deptnno
    2. The number of employees in each department, ecept those without a deptno
    3. The total number of employees, including those without a deptno
    4. The total number of employees, except those without a deptno
28. Which assertion about the following quires is true> SELECT COUNT(DISTICT mgr), MAX(DISTINCT salary) from emp; SELECT COUNT (ALL mgr), MAX(ALL salary) FROM emp;
    1. They will always return the same numbers in columns 1 and 2
    2. They may return different numbers in column 1 but will always return the same number in column 2
    3. They may return different numbers in column 1 and may return different numbers in column 2
    4. They will always return the same number in column 1 but may return different numbers in column 2
29. What is the limit on the number of values a subquery using the IN operator can return to the parent query?
    1. 1
    2. 32,764
    3. unlimited
    4. 0
30. When using multiple tables to query information, in which clause do you specify the table names?
    1. HAVING
    2. GROUP BY
    3. WHERE
    4. FROM
31. The contents of the CONTESTANTS table are listed as follows: NAME AGE COUNTRY ---------------- -------------- --------------- BERTRAND 24 FRANCE GONZALEZ 29 SPAIN HEINRICH 22 GERMANY TAN 39 CHINA SVENSKY
32. RUSSIA SOO 21 You issue the following query against this table: SELECT NAME FROM CONTESTANT WHERE (COUNTRY, AGE) IN ( SELECT COUNTRY, MIN(AGE) FROM CONTESTANT GROUP BY COUNTRY); What is the result?
    1. SOO
    2. HEINRICH
    3. BERTRAND
    4. GONZALEZ
33. To delete any constraint from the table we have to use command
    1. Drop Constraint
    2. Delet
    3. Alter *
    4. Truncate
34. All the operators are used in single row subquery except one
    1. Between and
    2. <>
    3. =
    4. in
35. All the commands executes in iSQLplus except one
    1. Column
    2. Compute
    3. define
    4. Accept
36. Ascript file which will be executed automatically in iSQLPlus is
    1. afiedtbuf
    2. loginsql
    3. both a and b
    4. none of the above
37. A command in iSQL plus is used to give the status of old and new value of variable
    1. set feedback
    2. set verify
    3. set confirm
    4. none of the above
38. All commands are used to save the changes of the transaction except one
    1. Commit
    2. exitting from sqlplus
    3. DDL command
    4. savepoint
    5. none of the above
39. A Clause which is used in joining two tables other than equality operator is
    1. join
    2. on
    3. in
    4. using
40. A Clause which is the pseudocolumn used to know the current value of the sequence
    1. nextval
    2. current_val
    3. currval
    4. none of the above
41. A Query which is used in top-N analysis is
    1. subquery
    2. correlated subquery
    3. inline query
    4. outer query
42. An operator is used to get and display the redundant records
    1. Union all
    2. Distinct
    3. Union
    4. Intersect
43. All the datatypes with respect to Oracle 9i is true except one
    1. DATE
    2. TIMESTAMP
    3. TIMSTAMP with TIME ZONE
    4. TIMESTAMP WITH LOCAL TIME ZONE
    5. None of the above
    
    

INFORMATICA INTERMEDIATE TUNING GUIDELINES

The following numbered items are for intermediate level tuning.
After going through all the pieces above, and still having trouble, these are some things to look for. These are items within a map which make a difference in performance (We've done extensive performance testing of Informatica to be able to show these affects).
Keep in mind - at this level, the performance isn't affected unless there are more than 1 Million rows (average size: 2.5 GIG of data).
ALL items are Informatica MAP items, and Informatica Objects - none are outside the map. Also remember, this applies to PowerMart /PowerCenter (4.5x, 4.6x, / 1.5x, 1.6x) - other versions have NOT been tested. The order of these items is not relevant to speed. Each one has it's own impact on the overall performance. Again, throughput is also gauged by the number of objects constructed within a map/maplet. Sometimes it's better to sacrifice a little readability, for a little speed. It's the old paradigm, weighing readability and maintainability (true modularity) against raw speed. Make sure the client agrees with the approach, or that the data sets are large enough to warrant this type of tuning.
BE AWARE: The following tuning tips range from "minor" cleanup to "last resort" types of things - only when data sets get very large, should these items be addressed, otherwise, start with the BASIC tuning , then work your way in to these suggestions.
To understand the intermediate section, you'll need to review this tips.

1. Filter Expressions - try to evaluate them in a port expression. Try to create the filter (true/false) answer inside a port expression upstream. Complex filter expressions slow down the mapping. Again, expressions/conditions operate fastest in an Expression Object with an output port for the result. Turns out - the longer the expression, or the more complex - the more severe the speed degradation. Place the actual expression (complex or not) in an EXPRESSION OBJECT upstream from the filter. Compute a single numerical flag: 1 for true, 0 for false as an output port. Pump this in to the filter - you should see the maximum performance ability with this configuration.
   
2. Remove all "DEFAULT" value expressions where possible. Having a default value - even the "ERROR(xxx)" command slows down the session. It causes an unnecessary evaluation of values for every data element in the map. The only time you want to use "DEFAULT value is when you have to provide a default value for a specific port. There is another method: placing a variable with an IIF(xxxx, DEFAULT VALUE, xxxx) condition within an expression. This will always be faster (if assigned to an output port) than a default value.
   
3. Variable Ports are "slower" than Output Expressions. Whenever possible, use output expressions instead of variable ports. The variables are good for "static - and state driven" but do slow down the processing time - as they are allocated/reallocated each pass of a row through the expression object.
   
4. Datatype conversion - perform it in a port expression. Simply mapping a string to an integer, or an integer to a string will perform the conversion, however it will be slower than creating an output port with an expression like: to_integer(xxxx) and mapping an integer to an integer. It's because PMServer is left to decide if the conversion can be done mid-stream which seems to slow things down.
   
5. Unused Ports. Surprisingly, unused output ports have no affect on performance. This is a good thing. However in general it is good practice to remove any unused ports in the mapping, including variables. Unfortunately - there is no "quick" method for identifying unused ports.
   
6. String Functions. String functions definitely have an impact on performance. Particularly those that change the length of a string (substring, ltrim, rtrim, etc..). These functions slow the map down considerably, the operations behind each string function are expensive (de-allocate, and re-allocate memory within a READER block in the session). String functions are a necessary and important part of ETL, we do not recommend removing their use completely, only try to limit them to necessary operations. One of the ways we advocate tuning these, is to use "varchar/varchar2" data types in your database sources, or to use delimited strings in source flat files (as much as possible). This will help reduce the need for "trimming" input. If your sources are in a database, perform the LTRIM/RTRIM functions on the data coming in from a database SQL statement, this will be much faster than operationally performing it mid-stream.
   
7. IIF Conditionals are costly. When possible - arrange the logic to minimize the use of IIF conditionals. This is not particular to Informatica, it is costly in ANY programming language. It introduces "decisions" within the tool, it also introduces multiple code paths across the logic (thus increasing complexity). Therefore - when possible, avoid utilizing an IIF conditional - again, the only possibility here might be (for example) an ORACLE DECODE function applied to a SQL source.
   
8. Sequence Generators slow down mappings. Unfortunately there is no "fast" and easy way to create sequence generators. The cost is not that high for using a sequence generator inside of Informatica, particularly if you are caching values (cache at around 2000) - seems to be the suite spot. However - if at all avoidable, this is one "card" up a sleve that can be played. If you don't absolutely need the sequence number in the map for calculation reasons, and you are utilizing Oracle, then let SQL*Loader create the sequence generator for all Insert Rows. If you're using Sybase, don't specify the Identity column as a target - let the Sybase Server generate the column. Also - try to avoid "reusable" sequence generators - they tend to slow the session down further, even with cached values.
   
9. Test Expressions slow down sessions. Expressions such as: IS_SPACES tend slow down the mappings, this is a data validation expression which has to run through the entire string to determine if it is spaces, much the same as IS_NUMBER has to validate an entire string. These expressions (if at all avoidable) should be removed in cases where it is not necessary to "test" prior to conversion. Be aware however, that direct conversion without testing (conversion of an invalid value) will kill the transformation. If you absolutely need a test expression for numerics, try this: IIF( * 1 >= 0,,NULL) preferably you don't care if it's zero. An alpha in this expression should return a NULL to the computation. Yes - the IIF condition is slightly faster than the IS_NUMBER - because IS_NUMBER parses the entire string, where the multiplication operator is the actual speed gain.
   
10. Reduce Number of OBJETS in a map. Frequently, the idea of these tools is to make the "data translation map" as easy as possible. All to often, that means creating "an" (1) expression for each throughput/translation (taking it to an extreme of course). Each object adds computational overhead to the session and timings may suffer. Sometimes if performance is an issue / goal, you can integrate several expressions in to one expression object, thus reducing the "object" overhead. In doing so - you could speed up the map.
    
11. Update Expressions - Session set to Update Else Insert. If you have this switch turned on - it will definitely slow the session down - Informatica performs 2 operations for each row: update (w/PK), then if it returns a ZERO rows updated, performs an insert. The way to speed this up is to "know" ahead of time if you need to issue a DD_UPDATE or DD_INSERT inside the mapping, then tell the update strategy what to do. After which you can change the session setting to: INSERT and UPDATE AS UPDATE or UPDATE AS INSERT.
    
12. Multiple Targets are too slow. Frequently maps are generated with multiple targets, and sometimes multiple sources. This (despite first appearances) can really burn up time. If the architecture permits change, and the users support re-work, then try to change the architecture -> 1 map per target is the general rule of thumb. Once reaching one map per target, the tuning get's easier. Sometimes it helps to reduce it to 1 source and 1 target per map. But - if the architecture allows more modularization 1 map per target usually does the trick. Going further, you could break it up: 1 map per target per operation (such as insert vs update). In doing this, it will provide a few more cards to the deck with which you can "tune" the session, as well as the target table itself. Going this route also introduces parallel operations. For further info on this topic, see my architecture presentations on Staging Tables, and 3rd normal form architecture (Corporate Data Warehouse Slides).
    
13. Slow Sources - Flat Files. If you've got slow sources, and these sources are flat files, you can look at some of the following possibilities. If the sources reside on a different machine, and you've opened a named pipe to get them across the network - then you've opened (potentially) a can of worms. You've introduced the network speed as a variable on the speed of the flat file source. Try to compress the source file, FTP PUT it on the local machine (local to PMServer), decompress it, then utilize it as a source. If you're reaching across the network to a relational table - and the session is pulling many many rows (over 10,000) then the source system itself may be slow. You may be better off using a source system extract program to dump it to file first, then follow the above instructions. However, there is something your SA's and Network Ops folks could do (if necessary) - this is covered in detail in the advanced section. They could backbone the two servers together with a dedicated network line (no hubs, routers, or other items in between the two machines). At the very least, they could put the two machines on the same sub-net. Now, if your file is local to PMServer but is still slow, examine the location of the file (which device is it on). If it's not on an INTERNAL DISK then it will be slower than if it were on an internal disk (C drive for you folks on NT). This doesn't mean a unix file LINK exists locally, and the file is remote - it means the actual file is local.
    
14. Too Many Aggregators. If your map has more than 1 aggregator, chances are the session will run very very slowly - unless the CACHE directory is extremely fast, and your drive seek/access times are very high. Even still, placing aggregators end-to-end in mappings will slow the session down by factors of at least 2. This is because of all the I/O activity being a bottleneck in Informatica. What needs to be known here is that Informatica's products: PM / PC up through 4.7x are NOT built for parallel processing. In other words, the internal core doesn't put the aggregators on threads, nor does it put the I/O on threads - therefore being a single strung process it becomes easy for a part of the session/map to become a "blocked" process by I/O factors. For I/O contention and resource monitoring, please see the database/datawarehouse tuning guide.
    
15. Maplets containing Aggregators. Maplets are a good source for replicating data logic. But just because an aggregator is in a maplet doesn't mean it won't affect the mapping. The reason maplets don't affect speed of the mappings, is they are treated as a part of the mapping once the session starts - in other words, if you have an aggregator in a maplet, followed by another aggregator in a mapping you will still have the problem mentioned above in #14. Reduce the number of aggregators in the entire mapping (included maplets) to 1 if possible. If necessary, split the map up in to several different maps, use intermediate tables in the database if required to achieve processing goals.
    
16. Eliminate "too many lookups". What happens and why? Well - with too many lookups, your cache is eaten in memory - particularly on the 1.6 / 4.6 products. The end result is there is no memory left for the sessions to run in. The DTM reader/writer/transformer threads are not left with enough memory to be able to run efficiently. PC 1.7, PM 4.7 solve some of these problems by caching some of these lookups out to disk when the cache is full. But you still end up with contention - in this case, with too many lookups, you're trading in Memory Contention for Disk Contention. The memory contention might be worse than the disk contention, because the system OS end's up thrashing (swapping in and out of TEMP/SWAP disk space) with small block sizes to try to locate "find" your lookup row, and as the row goes from lookup to lookup, the swapping / thrashing get's worse.
    
17. Lookups & Aggregators Fight. The lookups and the aggregators fight for memory space as discussed above. Each requires Index Cache, and Data Cache and they "share" the same HEAP segments inside the core. See Memory Layout document for more information. Particularly in the 4.6 / 1.6 products and prior - these memory areas become critical, and when dealing with many many rows - the session is almost certain to cause the server to "thrash" memory in and out of the OS Swap space. If possible, separate the maps - perform the lookups in the first section of the maps, position the data in an intermediate target table - then a second map reads the target table and performs the aggregation (also provides the option for a group by to be done within the database)... Another speed improvement...