Tested 11g OLTP compression and found rather serious issue
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 fromdba_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 fromdba_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 fromdba_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