Four Partitions, a 12 GB Sort Spill, and a 38-Minute Cache Build
An 18 million-row Oracle source feeds an Oracle target warehouse on a different database server. The mapping contains a Source Qualifier with a three-table join, an Expression, a connected Lookup on a 2.1 million-row dimension, an Aggregator with 'Sorted Input' enabled, and a Sorter before the Aggregator. The session is configured with four source partitions and four target partitions, DTM buffer size 64 MB, commit interval 50,000, and default lookup cache settings. Runtime is 4 hours 20 minutes. The session log shows 38 minutes building the lookup cache on every run, a 12 GB sorter spill, and long buffer-pool waits. The source database shows repeated full scans of the joined tables. The target is idle about 60% of the time. The lookup dimension changes only once a week, but the cache is rebuilt at every run. Management wants the load under two hours without adding hardware.
1. Rank the four bottlenecks by expected impact and justify the order from the symptoms.
Read the worked answer
The target is idle 60% of the time, so it is not the constraint. The work is backing up upstream. The largest controllable cost is the 12 GB sorter spill: it is disk I/O on the critical path and it feeds an Aggregator that then has to process sorted data. Second is the repeated 38-minute lookup cache build, which is pure repeated work and can be reduced or persisted. Third is partition effectiveness: four partitions may be skewed or may be adding repartitioning overhead around the Sorter and Aggregator. Fourth is DTM buffer size and commit interval; buffer-pool waits can indicate undersized buffers, but increasing memory without removing the spill only postpones the problem.
The order I would investigate is: remove or shrink the sort by pushing ORDER BY and aggregation to the source or disabling Sorted Input; reduce the lookup to current rows with a SQL override and use a persistent cache; measure per-partition rows and fix skew or unnecessary partition points; tune DTM buffer and commit interval. The source full scans suggest the join and filter are also not selective, so add the filter early and verify the join order in the source SQL.
2. Which mapping and session changes would you make first, and which are risky?
Read the worked answer
First, reduce data volume before the expensive operations. Add the selective filter in the Source Qualifier or in the source SQL, not after the Joiner. If the three-table join is in the same source database, keep it in the Source Qualifier and add the filter there so Oracle can use indexes. Replace the Sorter plus Sorted Input with a database ORDER BY only if the source can return the order cheaply; otherwise disable Sorted Input and let the Aggregator group the data. Better, push the aggregation into the source SQL as a manual pushdown if the group-by is simple and the source can handle it.
Second, change the lookup: add the current-row predicate to the lookup SQL override, reducing 2.1 million rows substantially, and set the cache to persistent with a weekly rebuild. Size the cache after the filter.
Risky changes: adding ORDER BY to a large source can force a database sort and make things worse, so test the plan. Full pushdown optimization can change semantics for outer joins and sequence-generated columns. Increasing DTM buffer to 256 MB is low risk but may just hide the spill. Changing partition keys can break Rank or Aggregator correctness, so validate row counts after each change.
3. How would you prove whether the four partitions are helping or hurting?
Read the worked answer
Run a controlled comparison on the same input: one partition versus four, with the same buffer and lookup settings. Capture the session log's per-partition row counts and run times. If the four-partition run is not faster, or if one partition has most of the rows, partitioning is not helping. Check the partition key: hashing on a low-cardinality or skewed column concentrates rows. Use a high-cardinality key such as order_id, or round-robin if no co-location is needed.
Check partition points. Repartitioning just before an Aggregator or Rank can force a sort and a redistribution; place partition points so that the expensive transformation receives data already partitioned on its group-by key. If Rank is used, partition by region_id so each region is in one partition, then validate that the total output is still three rows per region. Look for lookup cache duplication per partition; if each partition builds its own cache, the 38-minute build is multiplied.
Finally, measure the source and target independently. If the source query is the bottleneck, partitioning the session will not fix it; tune the SQL and indexes first. Keep the four-partition configuration only if per-partition throughput increases and the result set is correct.