Re: OLTP compression slow update
Date: Thu, 27 Dec 2012 19:07:51 -0800
Message-ID: <CAENwkM4hvCGuLcFthacTP5c3Yu-fxyc1c1hCPigr=Jb0EoSSog_at_mail.gmail.com>
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_at_yahoo.com>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.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 28 2012 - 04:07:51 CET