Re: OLTP compression slow update - RESOLVED

From: Michael Dinh <mdinh235_at_gmail.com>
Date: Thu, 27 Dec 2012 22:12:36 -0800
Message-ID: <CAENwkM7wxu8PsUVJhtPCjnoSdLazZqd_uxicEcoE+4EuWd-gNQ_at_mail.gmail.com>

One more test for
ORIGINAL tables DEFAULT PCTFREE 10, INITRANS, PK Index

Run1 - No Compression, Run2 - OLTP Compression

exec runstats_pkg.rs_stop(0,'WORKLOAD'); Run1 ran in 6327 hsecs
Run2 ran in 5240 hsecs
run 1 ran in 120.74% of the time

Name                                                Run1
Run2        Diff
STAT...sorts (memory)                                 35
42               7
STAT...sorts (rows)                                    0
58              58
STAT...Elapsed Time                                6,329
5,242          -1,087
STAT...CPU used by this session                   21,342
32,189          10,847
STAT...recursive cpu usage                        20,794
31,700          10,906
STAT...physical reads direct                     543,836
515,788         -28,048
STAT...physical reads                            543,836
636,685          92,849
STAT...physical reads cache                            0
120,897         120,897
STAT...session logical reads                   1,526,002
4,836,131       3,310,129
STAT...redo size                           1,109,349,356     923,561,220

-185,788,136

Run1 latches total versus runs -- difference and pct

Run1            Run2            Diff       Pct
10,544,888      21,785,054      11,240,166     48.40%

PL/SQL procedure successfully completed.

LATCH.cache buffers chains                     5,400,273
16,367,773      10,967,500

On Thu, Dec 27, 2012 at 9:28 PM, Michael Dinh <mdinh235_at_gmail.com> wrote:

> 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
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 28 2012 - 07:12:36 CET