Exadata上的HCC测试（EHCC）—2—：DBMS_COMPRESSION.GET_COMPRESSION_RATIO
Exadata上的HCC测试（EHCC）—3—分区表的压缩
Oracle 从8.1.5开始引入了“Index key compression”压缩，也就是重复的索引键值可以被压缩。
.
从9.2开始引入了“Data segment compression”，也就是我们说的“Basic compression”，但是该功能只有在批量数据加载时才生效（比如直接路径装载，CTAS等）。
.
从Oracle11.1版本引入了Advanced Compression，支持OLTP的压缩，也就是说可以支持INSERT,DELETE,UPDATE操作的压缩。
(正式发布的11g最早版本是2008年OOW上的11.1.0.6.x，2009年我就有客户用这个版本的了，而且一次上了4中平台的RAC，O(∩_∩)O哈哈~)
.
当然，从11g开始，Oracle很多其他操作也支持压缩，比如，非结构化数据压缩（SecureFile数据压缩），Data Pump数据压缩，以及RMAN备份压缩等等，这里不做赘述。
支持OLTP的压缩，不代表每个DML语句执行时都会启用同步压缩，也就是说，只有当数据块中未压缩的数据达到一个阀值的时候，才会启动压缩，而不是每个DML语句之后都会压缩。如图:
关于压缩表的限制：
1)Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used. 2)Compressed tables must not have more than 255 columns. 3)Compression is not applied to lob segments. 4)Table compression is only valid for heap organized tables, not index organized tables. 5)The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings. 6)Table compression cannot be specified for external or clustered tables.
更多内容请参考 Notes 882712.1。
这里主要讨论下EHCC（Exadata Hybrid Columnar Compression ），官方文档说明如下：
关于测试创建各类压缩表的创建消耗的时间和IO的统计如下，我就不说了，有兴趣的自己看吧，O(∩_∩)O哈哈~
--- OLTP method
SQL> create table lunar_comp_classic compress as select * from LUNARTEST where 1=2;
Table created.
Elapsed: 00:00:00.03
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
2 where name = 'cell physical IO interconnect bytes returned by smart scan';
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.01
SQL> select name,value/1024/1024 as mb from v$statname
2 natural join v$mystat where name in
3 (
4 'physical read total bytes',
5 'cell physical IO interconnect bytes',
6 'cell physical IO interconnect bytes returned by smart scan'
7 );
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 0
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.01
SQL> alter table lunar_comp_classic nologging;
Table altered.
Elapsed: 00:00:00.01
SQL>
SQL> insert /*+ append */ into lunar_comp_classic select * from LUNARTEST;
commit;
select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';
select name,value/1024/1024 as mb from v$statname
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
20000000 rows created.
Elapsed: 00:00:31.97
SQL>
Commit complete.
Elapsed: 00:00:00.01
SQL> 2
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> SQL> 2 3 4 5 6 7
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 1463.29688
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQ
--OLTP.
SQL> create table lunar_comp_oltp compress for oltp as select * from LUNARTEST where 1=2;
insert into lunar_comp_oltp select * from LUNARTEST;
commit;
Table created.
Elapsed: 00:00:00.02
SQL>
select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';
select name,value/1024/1024 as mb from v$statname
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
20000000 rows created.
Elapsed: 00:01:50.14
SQL>
Commit complete.
Elapsed: 00:00:00.14
SQL> SQL> 2
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> 2 3 4 5 6 7
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 0
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL>
--QUERY LOW method.
SQL> create table lunar_comp_query_low compress for query low as select * from LUNARTEST where 1=2;
alter table lunar_comp_query_low nologging;
insert /*+ append */ into lunar_comp_query_low select * from LUNARTEST;
Table created.
Elapsed: 00:00:00.02
SQL> commit;
Table altered.
Elapsed: 00:00:00.00
SQL>
select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';
select name,value/1024/1024 as mb from v$statname
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
20000000 rows created.
Elapsed: 00:00:20.33
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL> SQL> SQL> SQL> 2
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> 2 3 4 5 6 7
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 600.71875
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL>
--QUERY HIGH method.
SQL> create table lunar_comp_query_high compress for query high as select * from LUNARTEST where 1=2;
alter table lunar_comp_query_high nologging;
insert /*+ append */ into lunar_comp_query_high select * from LUNARTEST;
commit;
select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';
select name,value/1024/1024 as mb from v$statname
natural join v$mystat where name in
(
Table created.
Elapsed: 00:00:00.07
SQL> 'physical read total bytes',
Table altered.
Elapsed: 00:00:00.01
SQL> 'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
20000000 rows created.
Elapsed: 00:00:29.26
SQL>
Commit complete.
Elapsed: 00:00:00.10
SQL> SQL> SQL> 2
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> 2 3 4 5 6 7
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 14.3125
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> SQL>
--ARCHIVE LOW method.
SQL> create table lunar_comp_archive_low compress for archive low as select * from LUNARTEST where 1=2;
alter table lunar_comp_archive_low nologging;
insert /*+ append */ into lunar_comp_archive_low select * from LUNARTEST;
commit;
select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';
select name,value/1024/1024 as mb from v$statname
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
Table created.
Elapsed: 00:00:00.20
SQL>
Table altered.
Elapsed: 00:00:00.20
SQL>
20000000 rows created.
Elapsed: 00:00:28.07
SQL>
Commit complete.
Elapsed: 00:00:00.00
SQL> SQL> 2
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.01
SQL> SQL> 2 3 4 5 6 7
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 14.3125
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> SQL> SQL> SQL>
--ARCHIVE HIGH method.
SQL> create table lunar_comp_archive_high compress for archive high as select * from LUNARTEST where 1=2;
alter table lunar_comp_archive_high nologging;
insert /*+ append */ into lunar_comp_archive_high select * from LUNARTEST;
commit;
Table created.
Elapsed: 00:00:00.02
SQL>
Table altered.
Elapsed: 00:00:00.01
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';
select name,value/1024/1024 as mb from v$statname
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
20000000 rows created.
Elapsed: 00:04:01.13
SQL>
Commit complete.
Elapsed: 00:00:00.01
SQL> SQL> 2
NAME MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL> 2 3 4 5 6 7
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
cell physical IO interconnect bytes 9.546875
cell physical IO interconnect bytes returned by smart scan 0
Elapsed: 00:00:00.00
SQL> SQL>
LUNAR@mylove>SELECT SEGMENT_NAME,
2 BYTES/1024/1024 MB
3 FROM USER_SEGMENTS
4 WHERE SEGMENT_NAME LIKE 'LUNAR%'or SEGMENT_NAME LIKE 'LUNAR_%';
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
LUNARTEST 1472
LUNAR_COMP_ARCHIVE_HIGH 5
LUNAR_COMP_ARCHIVE_LOW 8
LUNAR_COMP_BASIC 736
LUNAR_COMP_OLTP 904
LUNAR_COMP_QUERY_HIGH 8
LUNAR_COMP_QUERY_LOW 304
7 rows selected.
Elapsed: 00:00:00.08
LUNAR@mylove>
LUNAR@mylove>set lines 155
LUNAR@mylove>compute sum of totalsize_megs on report
LUNAR@mylove>break on report
LUNAR@mylove>col owner for a10
LUNAR@mylove>col segment_name for a30
LUNAR@mylove>col segment_type for a10
LUNAR@mylove>col totalsize_megs for 999,999.9
LUNAR@mylove>col compression_ratio for 999.9
LUNAR@mylove>select owner, segment_name, segment_type type,
2 sum(bytes/1024/1024) as totalsize_megs,
3 &original_size/sum(bytes/1024/1024) as compression_ratio
4 from dba_segments
5 where owner='LUNAR'
6 and segment_name like 'LUNAR%'
7 and segment_type ='TABLE'
8 group by owner, segment_name, tablespace_name, segment_type
9 order by 5;
Enter value for original_size: 1472
old 3: &original_size/sum(bytes/1024/1024) as compression_ratio
new 3: 1472/sum(bytes/1024/1024) as compression_ratio
OWNER SEGMENT_NAME TYPE TOTALSIZE_MEGS COMPRESSION_RATIO
---------- ------------------------------ ------------------ -------------- -----------------
LUNAR LUNARTEST TABLE 1,472.0 1.0
LUNAR LUNAR_COMP_OLTP TABLE 904.0 1.6
LUNAR LUNAR_COMP_BASIC TABLE 736.0 2.0
LUNAR LUNAR_COMP_QUERY_LOW TABLE 304.0 4.8
LUNAR LUNAR_COMP_QUERY_HIGH TABLE 8.0 184.0
LUNAR LUNAR_COMP_ARCHIVE_LOW TABLE 8.0 184.0
LUNAR LUNAR_COMP_ARCHIVE_HIGH TABLE 5.0 294.4
--------------
sum 3,437.0
7 rows selected.
Elapsed: 00:00:00.08
LUNAR@mylove>
