Re: Tested 11g OLTP compression and found rather serious issue

From: Tamkatten_at_gmail.com <tamkatten_at_gmail.com>
Date: Fri, 31 Jul 2009 09:35:45 -0700 (PDT)
Message-ID: <654862ac-b509-4c26-ac93-6e0bfcca3d42_at_b15g2000yqd.googlegroups.com>



On 31 Jul., 15:46, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jul 31, 5:02 am, "Tamkat..._at_gmail.com" <tamkat..._at_gmail.com> wrote:
>
> > Oh, I forgot this important info, regret :
>
> > SQL> select * from v$version;
>
> > BANNER
> > ---------------------------------------------------------------------------­­-----
> > Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
> > PL/SQL Release 11.1.0.7.0 - Production
> > CORE    11.1.0.7.0      Production
> > TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
> > NLSRTL Version 11.1.0.7.0 - Production
>
> > - Kenneth Koenraadt
>
> Ken, I do not know how table compression works on 11g but when first
> introduced if you updated a row in a compressed table Oracle stored
> the updated row back in uncompressed format.  Compression was
> suggested for tables what had little to no update activity.  Your
> results could be partially due to migrated rows if no enhancement to
> recompress the row was not added in 11g.
>
> Interested in any updates you come up with.
>
> HTH -- Mark D Powell --

Hi Mark,

Tried to run the test case again with an "ordinarily" compressed table :

create table c2 compress tablespace t1 as select * from dba_objects where 0 = 1;

The result was quite interesting :

 The compress ratios were 0.48, 0.48 and 1.19 respectively above, with an OLTP compressed table.

 With ordinary compression, they were 0.91, 0.91 and 0.91 respectively!

So while initially less efficient, ordinary compression seems much more robust to updates than OLTP compression. A little surprising.

The whitepaper on OLTP claims that OLTP compression works on the block level, entirely different than ordinary compression.

On the other hand, it also claims that OLTP compression works "between" transactions and compresses a block in batches when certain thresholds are reached.

So if the test above was extended with further updates on table C2, it may actually shrink again ?

Then, of course, if the compression ratio fluctuates between 50 and 120% as above, how much is OLTP compression worth ? You would need to reserve not 50% of the original storage, but 120%.

More tests are required, I guess.

  • Kenneth Koenraadt
Received on Fri Jul 31 2009 - 11:35:45 CDT

Original text of this message