Re: OLTP compression slow update

From: Michael Dinh <mdinh235_at_gmail.com>
Date: Thu, 27 Dec 2012 21:03:39 -0800
Message-ID: <CAENwkM4446w+emSo4x_yFo0M_+2fPkRBjq7X4=7B23QXRH8WGA_at_mail.gmail.com>

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

Received on Fri Dec 28 2012 - 06:03:39 CET