Re: OLTP compression slow update - RESOLVED
Date: Thu, 27 Dec 2012 21:28:39 -0800
Message-ID: <CAENwkM6MWOys1tz0bEZyXQe5dZTnEM+tWCi1_iyvUsQJC4FCRA_at_mail.gmail.com>
Performance is the price paid for protection. Thank you everyone for you assistance and guidance.
Summary of changes made:
Create PK REVERSE index PCTFREE 20 INITRANS 8 to reduce LATCH.cache buffers
chains
[db_block_checking] changed from [MEDIUM] to [FALSE]
[db_lost_write_protect] changed from [TYPICAL] to [NONE]
alter session force parallel dml parallel 8; Run1 - No Compression, Run2 - OLTP Compression
exec runstats_pkg.rs_stop(0,'WORKLOAD');
Run1 ran in 5728 hsecs
Run2 ran in 5158 hsecs
run 1 ran in 111.05% of the time
Name Run1 Run2 Diff STAT...Elapsed Time 5,730 5,160 -570 STAT...CPU used by this session 28,864 39,164 10,300 STAT...recursive cpu usage 28,388 38,695 10,307 STAT...physical reads 612,670 221,574 -391,096 STAT...physical reads direct 612,670 221,574 -391,096 STAT...session logical reads 1,594,872 2,082,986 488,114 STAT...redo size 1,114,780,236 603,843,844-510,936,392
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct 9,851,690 15,371,363 5,519,673 64.09%
PL/SQL procedure successfully completed.
LATCH.cache buffers chains 5,084,943 10,743,583 5,658,640
On Thu, Dec 27, 2012 at 9:03 PM, Michael Dinh <mdinh235_at_gmail.com> wrote:
> No matter how I slice and dice it, OLTP compression does not perform.
>
> alter session force parallel dml parallel 8;
> Run1 - No Compression, Run2 - OLTP Compression
>
> exec runstats_pkg.rs_stop(0,'WORKLOAD');
> Run1 ran in 5621 hsecs
> Run2 ran in 81024 hsecs
> run 1 ran in 6.94% of the time
> Name Run1
> Run2 Diff
> STAT...physical reads cache 9
> 15 6
> STAT...Elapsed Time 5,623
> 81,027 75,404
> STAT...recursive cpu usage 32,344
> 336,847 304,503
> STAT...CPU used by this session 33,607
> 355,431 321,824
> STAT...physical reads 612,679
> 221,589 -391,090
> STAT...physical reads direct 612,670
> 221,574 -391,096
> STAT...session logical reads 1,595,165
> 2,083,206 488,041
> STAT...redo size 1,114,914,828
> 608,192,304 -506,722,524
> Run1 latches total versus runs -- difference and pct
> Run1 Run2 Diff Pct
> 15,284,407 97,981,534 82,697,127 15.60%
> PL/SQL procedure successfully completed.
>
> LATCH.cache buffers chains 9,701,573
> 78,245,276 68,543,703
> Create PK REVERSE index PCTFREE 20 INITRANS 8 to reduce LATCH.cache
> buffers chains
> exec runstats_pkg.rs_stop(0,'WORKLOAD');
> Run1 ran in 5091 hsecs
> Run2 ran in 73544 hsecs
> run 1 ran in 6.92% of the time
> Name Run1
> Run2 Diff
> STAT...parse time cpu 2
> 3 1
> STAT...physical reads cache 4,942
> 7 -4,935
> STAT...Elapsed Time 5,094
> 73,548 68,454
> STAT...recursive cpu usage 24,477
> 303,051 278,574
> STAT...CPU used by this session 25,828
> 321,333 295,505
> STAT...physical reads direct 612,670
> 221,574 -391,096
> STAT...physical reads 617,612
> 221,581 -396,031
> STAT...session logical reads 1,595,522
> 2,083,432 487,910
> STAT...redo size 1,117,582,964
> 608,765,612 -508,817,352
> Run1 latches total versus runs -- difference and pct
> Run1 Run2 Diff Pct
> 10,184,848 25,302,099 15,117,251 40.25%
> PL/SQL procedure successfully completed.
>
> LATCH.cache buffers chains 5,187,214
> 11,617,039 6,429,825
>
> On Thu, Dec 27, 2012 at 7:07 PM, Michael Dinh <mdinh235_at_gmail.com> wrote:
>
>> 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.
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 28 2012 - 06:28:39 CET