玉面飞龙的BLOG

Try 11g OLTP compress

December 28th, 2007 | Categories: Boring | Tags:

Oracle11g增加了很多压缩的功能，如对OLTP操作下表压缩的完善，Data Pump 文件的压缩，standby日志传输的压缩，RMAN备份的压缩。

10g之前指定表的compress选项，只能在direct path load或者table move的时候进行压缩

11g中指定compress for all operations则增加了对表压缩的条件。如orawh所总结，

oracle改变了压缩的行为，不是每次发生数据变化都会去压缩，而是通过内部的threshold来控制压缩，并且压缩是由transaction触发的，当一个transaction触发DML操作，oracle会去根据threshold来判断是否需要对整个BLOCK进行compress，如果compress后又到达threshold，那么oracle会再去recompress整个BLOCK,直到oracle觉得没有可压缩的余地，而且只有触发compress的transaction会有一些压缩的代价。

测试如下，

SQL> create table test_compress (id number, name varchar2(3000)) compress for all operations;
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_compress values(i,lpad('test',3000,'x') );
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks from dba_tables where table_name='TEST_COMPRESS';
TABLE_NAME BLOCKS
---------- ----------
TEST_COMPRESS 118
SQL> drop table test_compress;
Table dropped.
SQL> create table test_compress (id number, name varchar2(3000)) compress for all operations;
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_compress values(i,lpad('test',3000,'x') );
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>commit;
Commit complete
SQL>analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks from dba_tables where table_name='TEST_COMPRESS';
TABLE_NAME BLOCKS
---------- ----------
TEST_COMPRESS 5032
SQL> alter table test_compress move;
Table altered.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL>select table_name,blocks from dba_tables where table_name='TEST_COMPRESS'
TABLE_NAME BLOCKS
---------- ----------
TEST_COMPRESS 59

如上测试，由transaction来触发压缩，并且只针对DML操作touch的block进行压缩。

且频繁的更新操作会导致表上有较多的Chained Rows。

SQL> create index idx2 on test_compress(id);
Index created.
SQL> begin
2 for i in 1..1000 loop
3 update test_compress set id=id+i+40000 where id=i;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks from dba_tables where table_name='TEST_COMPRESS';
TABLE_NAME BLOCKS
---------- ----------
TEST_COMPRESS 59
SQL> begin
2 for i in 1..10000 loop
3 if mod(i,5) = 0 then
4 update test_compress set id=id+i+40000,name=lpad('update',3000,'x') where id=i;
5 end if;
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks,CHAIN_CNT from dba_tables where table_name='TEST_COMPRESS';
TABLE_NAME BLOCKS CHAIN_CNT
---------- -------------- ----------
TEST_COMPRESS 1888 20673
SQL> select count(*) from TEST_COMPRESS;
COUNT(*)
----------
20000
SQL> alter table TEST_COMPRESS move;
Table altered.
SQL> alter index BINZHANG.IDX2 rebuild;
Index altered.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks,CHAIN_CNT from dba_tables where table_name='TEST_COMPRESS';
TABLE_NAME BLOCKS CHAIN_CNT
---------- ---------- ----------
TEST_COMPRESS 62 0