Buddy Yuan的个人技术博客

版权声明：本文为Buddy Yuan原创文章，未经允许不得转载。原文地址：使用dbms_compression.get_compression_ratio评估压缩比例

DBMS_COMPRESSION是用于管理Oracle数据库表压缩功能的包。它通过消除磁盘页中的重复值来压缩数据。随着数据库大小的增加，表压缩可以节省资源，它不仅可以节省磁盘，还可以减少缓冲区缓存中的内存使用量。DBMS_COMPRESSION是在11gR2中引入，这个软件包一直在不断发展，现在在12c版本中引入了一些非常有用的增强功能。我们来试用一下。

首先创建一个用户和表用于测试。

SQL> alter session set container=ORCLPDB1;
Session altered.

SQL> create user test1 identified by test1;
User created.

SQL> grant dba to test1;
Grant succeeded.

SQL> create tablespace testtbs datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/testtbs.dbf' size 100m autoextend on next 100m maxsize unlimited;
Tablespace created.

SQL> alter user test1 quota unlimited on testtbs;
User altered

SQL> connect test1/test1@ORCLPDB1
Connected.

SQL> create table testtab tablespace testtbs as select rownum id, a.* from dba_objects a where 1 = 0;
Table created.

接下来我们往表里面插入一百万数据。

declare
  l_cnt  number;
  l_rows number := 1000000;
begin
  insert /*+ append */ into testtab select rownum, a.* from dba_objects a;
  l_cnt := sql%rowcount;
  commit;
  while (l_cnt < l_rows) loop
     insert /*+ append */ into testtab
     select rownum+l_cnt, owner,object_name,subobject_name,object_id,data_object_id,object_type,created,last_ddl_time,timestamp,status,temporary,generated,
     secondary,namespace,edition_name,sharing,editionable,oracle_maintained,application,default_collation,duplicated,sharded,created_appid,created_vsnid,modified_appid,modified_vsnid
       from testtab a
      where rownum <= l_rows-l_cnt;
     l_cnt := l_cnt + sql%rowcount;
     commit;
  end loop;
  commit;
end;
/

让我们查询dba_segments目录视图，可以看到分配了160MB。

SQL> SELECT segment_name, SUM(bytes)/1024/1024 MB FROM dba_segments WHERE segment_name='TESTTAB' GROUP BY segment_name;

SEGMENT         MB
------- ----------
TESTTAB        160

现在在12c可以在使用dbms_compression包得到每个块的估计行数。DBMS_COMPRESSION包使用不同的数字常量来描述每种类型的可能压缩，现在可以从1到1000000,具体可以参考官方文档，如下列表所示:

当选择(comptype = 2)的时候执行的是COMP_ADVANCED。具体如下所示：

set serveroutput on
declare
  v_blocks_comp   pls_integer;
  v_blocks_uncomp   pls_integer;
  v_rows_comp   pls_integer;
  v_rows_uncomp   pls_integer;
  v_compress_ratio  number;
  v_compress_type   varchar2(32767);
begin
  dbms_compression.get_compression_ratio (
   scratchtbsname  => 'testtbs',
   ownname=>'test1',
   objname=>'testtab',
   subobjname=> null,
   comptype=> 2,
   blkcnt_cmp=> v_blocks_comp,
   blkcnt_uncmp=> v_blocks_uncomp,
   row_cmp=>v_rows_comp,
   row_uncmp=>v_rows_uncomp,
   cmp_ratio=>v_compress_ratio,
   comptype_str=>v_compress_type,
   subset_numrows=>dbms_compression.comp_ratio_minrows
  ) ;
  dbms_output.put_line('output: ');
  dbms_output.put_line('estimated compression ratio: ' || v_compress_ratio);
  dbms_output.put_line('blocks used - compressed sample: ' || v_blocks_comp);
  dbms_output.put_line('blocks used - uncompressed sample: ' || v_blocks_uncomp);
  dbms_output.put_line('rows in a block - compressed sample: ' || v_rows_comp);
  dbms_output.put_line('rows in a block - uncompressed sample: ' || v_rows_uncomp);
end;
/
Estimated Compression Ratio: 2.6
Blocks used - compressed sample: 915
Blocks used - uncompressed sample: 2432
Rows in a block - compressed sample: 129
Rows in a block - uncompressed sample: 48

这里可以计算出压缩比例未2.6%，使用这个级别的压缩可以使blocks从2432（uncompressed）到915(compressed)，压缩前是每个block存储48行，到压缩之后可以存储129行。
我们可以再次执行上述查询，把comptype=> 2修改成4，4是HCC Query High压缩方式。

结果如下：

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000000 rows
OUTPUT:
Estimated Compression Ratio: 23.1
Blocks used - compressed sample: 872
Blocks used - uncompressed sample: 20192
Rows in a block - compressed sample: 1147
Rows in a block - uncompressed sample: 49

可以看到，压缩比例提升到23.1%，使用这个级别的压缩可以使blocks从20192（uncompressed）到872(compressed)，压缩前是每个block存储49行，到压缩之后可以存储1147行。当然上面的提示也很明显，是做的EHCC压缩。当然这需要在Exadata才能压缩出这种比例。