今天有幸见识了下EXADATA的强大功能之一EHCC(Exadata Hybrid Columnar Compression),发现压缩效果确实很让人心动,压缩效率大大超过我的预计,压缩97%左右(1-628.1875/20573)
创建模拟表T_FF_SOURCE

14:32:52 SQL> create table t_FF_source as select * from dba_objects;
Table created.
Elapsed: 00:00:00.16
14:35:54 SQL> begin
14:37:05   2  for i in 1..100000 loop
14:37:05   3     insert into t_FF_source select * from dba_jects;
14:37:05   4    commit;
14:37:05   5   end loop;
14:37:05   6  end;
14:37:05   7   /
Elapsed: 00:13:07.76
14:51:05 SQL> select count(*) from t_FF_source;
  COUNT(*)
----------
 197015655
Elapsed: 00:00:33.18
14:51:56 SQL>  col segment_name format a45 heading "Segment Name"
14:52:55 SQL> select segment_name Segment_Name
14:52:55   2  ,      segment_type             "Segment Type"
14:52:55   3  ,      round(sum(bytes)/1024/1024/1024,2)     "Size In GB"
14:52:55   4  from dba_segments
14:52:55   5  where
14:52:56   6  segment_name ='T_FF_SOURCE'
14:52:56   7  group by segment_name,segment_type
14:52:56  8  order by 1;
Segment Name                                  Segment Type       Size In GB
--------------------------------------------- ------------------ ----------
T_FF_SOURCE                                   TABLE                   20.09

创建各种情况下压缩表

--BASE
create table t_FF_c  compress  NOLOGGING as select /*+ PARALLE 24*/ * from t_FF_source;
--OLTP
create table t_FF_c_o compress for oltp NOLOGGING as select /*+ PARALLE 24 */ * from t_FF_source;
--QUERY LOW
create table t_FF_q_l  compress for query low  NOLOGGING as select /*+ PARALLE 24 */ * from t_FF_source;
--QUERY HIGH
create table t_FF_q_h compress for query high parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source;
--ARCHIVE LOW
create table t_FF_a_l compress for archive low parallel 24 nologging as select /*+ PARALLE 12 */ * from t_FF_source;
--ARCHIVE HIGH
create table t_FF_a_h compress for archive high  parallel 24 nologging as select  /*+ PARALLE 12 */ * from t_FF_source;

其实BASE和OLTP是数据库基本的压缩功能,该功能不仅限于EXADATA,但是后面的四种压缩就是我们所说的EHCC,也只有EXADATA用户才能够体验到.

数据压缩结果

16:19:13 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
16:19:20   2  from dba_segments s,dba_tables t
16:19:20   3  where s.owner=t.owner and t.table_name=s.segment_name
16:19:20   4  and s.owner='FF' and t.table_name like 'T_FF%';
OWNER                          SEGMENT_NAME                            T_SIZE COMPRESS_FOR
------------------------------ ----------------------------------- ---------- ------------
FF                             T_FF_A_L                              1244.625 ARCHIVE LOW
FF                             T_FF_SOURCE                              20573
FF                             T_FF_Q_H                              1244.875 QUERY HIGH
FF                             T_FF_A_H                              628.1875 ARCHIVE HIGH
FF                             T_FF_C                                6961.625 BASIC
FF                             T_FF_Q_L                              2799.875 QUERY LOW
FF                             T_FF_C_O                             7759.1875 OLTP

试验结果证明
1.BASE也OLTP的压缩效率差不多(可能是因为BASIC的PCTFREE为0,OLTP的PCTFREE为10)
2.在EHCC的四种压缩中:QUERY LOW相对压缩率不高,采用LZO压缩算法,但是也比ORACLE自带的压缩效果高很多
3.QUERY HIGH和ARCHIVE LOW压缩率差不多,都是使用ZLIB压缩算法
4.ARCHIVE HIGH是压缩率极高,采用Bzip2压缩算法实现.

EXADATA EM性能监控