Re: OLTP compression slow update
- From: Michael Dinh <mdinh235@xxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Thu, 27 Dec 2012 15:36:24 -0800
Yes to Martin Question. An thanks for the contribution. Open SR as well.
Tables updated using ASSM and PCTFREE 20
sqltcompare.sql
Avg Elapsed Time in secs: 477.693 | 3276.149 (Uncompressed, OLTP
compression)
Avg CPU Time in secs: 195.690 | 3164.850 (Uncompressed, OLTP compression)
Avg User I/O Wait Time in secs: 288.419 | 118.845
Avg Buffer Gets: 1,603,505 | 2,081,777
Avg Disk Reads: 616,526 | 133,573
Ignite Monitoring
OLTP Compression:
Total Wait Time: 54.37m = 30.49(scattered read) + 15.02 (CPU) + 4.26 (log
file sync) + 3.53 (log file switch completion)
No Compression:
Total Wait Time: 7.57m = 7.56(scattered read) + 0.01 (CPU)
Current setting, will need to change as this could be contributing to the
performance for the price of protection.
db_block_checking=MEDIUM (FALSE DEFAULT)
db_block_checksum=TYPICAL (DEFAULT)
db_lost_write_protect=TYPICAL (NONE DEFAULT)
Using sqltcompare.sql and sqltxtract.sql nothing evident.
Snapper with interval 5 seconds, taking 1 snapshots shows for OLTP
compression:
TYPE, STATISTIC
,HDELTA, HDELTA/SEC, %TIME, GRAPH
STAT, HSC Compressed Segment Block Changes , 2.27k, 453.4,
STAT, HSC Heap Segment Block Changes , 2.27k, 453.4,
https://blogs.oracle.com/db/entry/master_note_for_oltp_compression
New pseudo code using merge verus NL.
MERGE INTO MD_FCT_NOCP f USING STG_ACCN_PROC_DAILY_UPD record ON (
f.SALE_PROC_DETAIL_KEY=record.SALE_PROC_DETAIL_KEY)
WHEN MATCHED THEN UPDATE 52 f columns)
1 execution, 911,861 rows processed, 1,603,505 buffer gets
MERGE INTO MD_FCT_OLTP f USING STG_ACCN_PROC_DAILY_UPD record ON (
f.SALE_PROC_DETAIL_KEY=record.SALE_PROC_DETAIL_KEY)
WHEN MATCHED THEN UPDATE 52 f columns)
1 execution, 911,861 rows processed, 2,081,777 buffer gets
On Thu, Dec 27, 2012 at 12:49 PM, Martin Berger
<martin.a.berger@xxxxxxxxx>wrote:
> Maybe we can sort the situation out a little bit.
>
> For me the main question is still "Why the execution X (compressed) is
> slower by 1.79 compared to execution Y (uncompressed)?"
> Is this still correct?
> I know of 2 methods oracle provide to answer these question:
> "system/session statistics" or "wait events". Does anyone knows other
> methods to get informations about the "what's going on"?
> Maybe Kevin or some others can give us some insight which statistics or
> events we can expect and measure to identify the process is 'in compression
> A' or 'doing compression task B'?
> If there are no such informations available I can only suggest to
> collect/sample stack traces.
>
> Jareds and Rajs suggestions are totally valid with a primary target to
> 'avoid the slow path', but I'd like to 'understand the slow path' and then
> decide how/why to act.
>
> just my .02
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts: