Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!193.201.147.86.MISMATCH!news.astraweb.com!border5.a.newsrouter.astraweb.com!feeder.news-service.com!postnews.google.com!j9g2000vbp.googlegroups.com!not-for-mail From: "Tamkatten@gmail.com" Newsgroups: comp.databases.oracle.server Subject: Re: Tested 11g OLTP compression and found rather serious issue Date: Sat, 8 Aug 2009 11:09:50 -0700 (PDT) Organization: http://groups.google.com Lines: 250 Message-ID: References: NNTP-Posting-Host: 90.184.0.232 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1249754990 1594 127.0.0.1 (8 Aug 2009 18:09:50 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sat, 8 Aug 2009 18:09:50 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: j9g2000vbp.googlegroups.com; posting-host=90.184.0.232; posting-account=yYFi_woAAAAMohWNfthQFDPMMXobT2or User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; InfoPath.2; .NET CLR 3.5.30729; .NET CLR 3.0.30618),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On 31 Jul., 10:50, "tamkat...@gmail.com" wrote: > 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=D5de 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 =3D 1; > > Tabel er oprettet. > > SQL> create table c2 compress for all operations tablespace t1 as > select * from dba_objects where 0 =3D 1; > > Tabel er oprettet. > > SQL> > SQL> > SQL> -- Simulate OLTP > SQL> declare > =A0 2 > =A0 3 =A0cursor c is > =A0 4 =A0select * from dba_objects; > =A0 5 =A0i binary_integer; > =A0 6 =A0j binary_integer; > =A0 7 =A0begin > =A0 8 > =A0 9 =A0for i in 1..10 loop > =A010 =A0 =A0for c_rec in c loop > =A011 =A0 =A0 =A0j :=3D j + 1; > =A012 =A0 =A0 =A0insert into c1 values c_rec; > =A013 =A0 =A0 =A0insert into c2 values c_rec; > =A014 =A0 =A0 =A0if mod(j,10) =3D 0 then > =A015 =A0 =A0 =A0 =A0commit; > =A016 =A0 =A0 =A0end if; > =A017 =A0 =A0end loop; > =A018 =A0 =A0end loop; > =A019 =A0end; > =A020 =A0/ > > PL/SQL-procedure er udf=B0rt. > > SQL> commit; > > Bekr=B5ftelse er udf=B0rt. > > SQL> > SQL> -- Calculate compress ratio > SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio =A0from > dba_segments a, dba_segments b > =A0 2 =A0where a.segment_name =3D 'C1' > =A0 3 =A0and b.segment_name =3D 'C2'; > > COMPRESS_RATIO > -------------- > =A0 =A0 =A0 =A0 =A0 =A0,48 > > SQL> > SQL> -- now update every row in compressed table, non-null column > SQL> > SQL> update c2 set object_id =3D object_id; > > 139820 r=B5kker er opdateret. > > SQL> commit; > > Bekr=B5ftelse er udf=B0rt. > > SQL> > SQL> -- Calculate compress ratio again > SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio =A0from > dba_segments a, dba_segments b > =A0 2 =A0where a.segment_name =3D 'C1' > =A0 3 =A0and b.segment_name =3D 'C2'; > > COMPRESS_RATIO > -------------- > =A0 =A0 =A0 =A0 =A0 =A0,48 > > SQL> > SQL> -- now update every row in compressed table, null column > SQL> update c2 set subobject_name =3D 'a'; > > 139820 r=B5kker er opdateret. > > SQL> commit; > > Bekr=B5ftelse er udf=B0rt. > > SQL> > SQL> -- Calculate compress ratio again > SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio =A0from > dba_segments a, dba_segments b > =A0 2 =A0where a.segment_name =3D 'C1' > =A0 3 =A0and b.segment_name =3D 'C2'; > > COMPRESS_RATIO > -------------- > =A0 =A0 =A0 =A0 =A0 1,19 > > SQL> > ********************* Hi again, Further findings on OLTP compression : 1) Reducing a column's width (e.g update mytable set col1 =3D substr (col1,1,trunc(length(col1)/2))) and then expanding it again seems to make the segment explode as well 2) You have a compressed table with app. 130.00 rows and 2 columns having exactly the same content. You then issue an update to set one of the columns equal to the other one. No logical change, though a physical one. The segment explodes again....case is below. As for now, I would be reluctant to use OLTP compression on even rarely updated tables. Any comments/findings ? ****************************** SQL> -- Create an ASSM tablespace with small extent size for testing SQL> drop tablespace t1 including contents and datafiles; Tabelomr=D5de er droppet. SQL> create tablespace t1 uniform size 128k; Tabelomr=D5de er oprettet. SQL> SQL> SQL> create table c1 (col1 varchar2(30),col2 number(10),col3 varchar2 (30),col4 number(10)) tablespace t1 ; Tabel er oprettet. SQL> SQL> create table c2 (col1 varchar2(30),col2 number(10),col3 varchar2 (30),col4 number(10)) compress for all operations Tabel er oprettet. SQL> SQL> SQL> SQL> SQL> -- Simulate OLTP SQL> declare 2 3 cursor c is 4 select object_id,object_name 5 from dba_objects; 6 7 i binary_integer; 8 j binary_integer; 9 10 begin 11 12 for i in 1..10 loop 13 for c_rec in c loop 14 j :=3D j + 1; 15 insert into c1 values (c_rec.object_name,c_rec.object_id,c_rec.object_name,c_rec.object_id); 16 insert into c2 values (c_rec.object_name,c_rec.object_id,c_rec.object_name,c_rec.object_id); 17 if mod(j,10) =3D 0 then 18 commit; 19 end if; 20 end loop; 21 end loop; 22 end; 23 / PL/SQL-procedure er udf=B0rt. SQL> SQL> commit; Bekr=B5ftelse er udf=B0rt. SQL> SQL> -- Calculate compress ratio SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio from 2 dba_segments a, dba_segments b 3 where a.segment_name =3D 'C1' 4 and b.segment_name =3D 'C2'; COMPRESS_RATIO -------------- ,71 SQL> SQL> commit; Bekr=B5ftelse er udf=B0rt. SQL> SQL> SQL> update c1 set col3 =3D col1; 131440 r=B5kker er opdateret. SQL> SQL> update c2 set col3 =3D col1; 131440 r=B5kker er opdateret. SQL> SQL> commit; Bekr=B5ftelse er udf=B0rt. SQL> SQL> SQL> select trunc(b.bytes/a.bytes,2) as compress_ratio from 2 dba_segments a, dba_segments b 3 where a.segment_name =3D 'C1' 4 and b.segment_name =3D 'C2'; COMPRESS_RATIO -------------- 1,2