版权声明:本文为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才能压缩出这种比例。
Trackbacks & Pingbacks 1
[…] Yuan原创文章,未经允许不得转载。原文地址:使用PostgreSQL分析操作系统命令及文件 […]
Post a Comment