Tested 11g OLTP compression and found rather serious issue

From: <tamkatten_at_gmail.com>
Date: Fri, 31 Jul 2009 01:50:11 -0700 (PDT)
Message-ID: <e8747c73-3f0b-46f8-8aec-d54e390f2e8f_at_r2g2000yqm.googlegroups.com>



Hi,

I ran the below script in a series of 11g OLTP compression tests. Note that the compression rate is fine right after the inserts. And after we update the OBJECT_ID column, the compression rate remains the same. But after we update a (mostly) NULL column (SUBOBJECT_NAME), the compressed segment *explodes* to a size even bigger than the uncompressed one! The latter update also takes much longer time to complete (no surprise).

Seems like a pitfall..Imagine a 100 Gb compressed table in production and we make an seemingly innocent update on it...

Any ideas? (No comments on the commits inside a loop, thank you, as mentioned below it is for simulating OLTP).

  • Kenneth Koenraadt

SQL> -- Create an ASSM tablespace with small extent size for testing SQL> create tablespace t1 uniform size 128k;

TabelomrÕde er oprettet.

SQL>
SQL> -- Create 2 logically identical tables, one non-compressed and one OLTP compressed

SQL> create table c1 tablespace t1 as select * from dba_objects where 0 = 1;

Tabel er oprettet.

SQL> create table c2 compress for all operations tablespace t1 as select * from dba_objects where 0 = 1;

Tabel er oprettet.

SQL>
SQL>
SQL> -- Simulate OLTP
SQL> declare

  2
  3 cursor c is
  4 select * from dba_objects;
  5 i binary_integer;
  6 j binary_integer;
  7 begin
  8
  9 for i in 1..10 loop
 10 for c_rec in c loop
 11      j := j + 1;
 12      insert into c1 values c_rec;
 13      insert into c2 values c_rec;
 14      if mod(j,10) = 0 then
 15        commit;
 16      end if;

 17 end loop;
 18 end loop;
 19 end;
 20 /

PL/SQL-procedure er udf°rt.

SQL> commit;

Bekrµftelse er udf°rt.

SQL>
SQL> -- Calculate compress ratio
SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio  from
dba_segments a, dba_segments b
  2 where a.segment_name = 'C1'
  3 and b.segment_name = 'C2';

COMPRESS_RATIO


           ,48

SQL>
SQL> -- now update every row in compressed table, non-null column
SQL>
SQL> update c2 set object_id = object_id;

139820 rµkker er opdateret.

SQL> commit;

Bekrµftelse er udf°rt.

SQL>
SQL> -- Calculate compress ratio again
SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio  from
dba_segments a, dba_segments b
  2 where a.segment_name = 'C1'
  3 and b.segment_name = 'C2';

COMPRESS_RATIO


           ,48

SQL>
SQL> -- now update every row in compressed table, null column
SQL> update c2 set subobject_name = 'a';

139820 rµkker er opdateret.

SQL> commit;

Bekrµftelse er udf°rt.

SQL>
SQL> -- Calculate compress ratio again
SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio  from
dba_segments a, dba_segments b
  2 where a.segment_name = 'C1'
  3 and b.segment_name = 'C2';

COMPRESS_RATIO


          1,19

SQL>


Received on Fri Jul 31 2009 - 03:50:11 CDT

Original text of this message