Kevin, Single block read was code generated from DI application. Hearing all sorts of limitations for DI app and it looks like ETL might have to be on the database end. When converted to MERGE using FTS, process completed in nearly 8 mins. Performance with OLTP compression is very negligible. FACT table has 33,179,386 rows and merging with STAGING table containing 911,861 rows. Oracle support suggest parallel 8 and completes in 13 minutes. That's no longer apple to apple comparison. NestedLoop No Compression: Executions 911,861 Rows Processed 911,861 Disk Reads 97,791 Buffer Gets 3,711,594 Wait time 1:10:59 NestedLoop Compression: Executions 911,861 Rows Processed 911,861 Disk Reads 146,649 Buffer Gets 4,599,848 Wait time 1:49:09 Merge (FTS) Compression: Executions 1 Rows Processed 911,861 Disk Reads 616,526 Buffer Gets 1,603,505 Wait time 00:07:57 Merge (FTS) Compression: Executions 1 Rows Processed 911,861 Disk Reads 133,573 Buffer Gets 2,081,777 Wait time 00:54:37 On Thu, Dec 27, 2012 at 4:09 PM, Kevin Closson <ora_kclosson@xxxxxxxxx>wrote: > > > >>For the broad base of reasonable cases, the elapsed time cost of reading > fewer physical blocks is likely to save overall elapsed time unless your > system is pretty badly CPU bound already. > > ...since the OP is OLTP, I'm confused why packed-payload would be seen as > a value prop. A single block read (db file sequential read) is a single > block read regardless of how much data is packed in it. Am I missing > something? > > For the broad base of reasonable cases, the elapsed time cost of reading > fewer physical blocks is likely to save overall elapsed time unless your > system is pretty badly CPU bound already. > > Using a rolling aging window as described below is an interesting way to > have your cake and eat it too. > -- //www.freelists.org/webpage/oracle-l