使用dbms_compression.get_compression_ratio評估壓縮比例
版權宣告:本文為Buddy Yuan原創文章,未經允許不得轉載。原文地址: ofollow,noindex" target="_blank">使用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_cntnumber; 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; SEGMENTMB ------- ---------- TESTTAB160
現在在12c可以在使用dbms_compression包得到每個塊的估計行數。DBMS_COMPRESSION包使用不同的數字常量來描述每種型別的可能壓縮,現在可以從1到1000000,具體可以參考 官方文件 ,如下列表所示:
當選擇(comptype = 2)的時候執行的是COMP_ADVANCED。具體如下所示:
set serveroutput on declare v_blocks_comppls_integer; v_blocks_uncomppls_integer; v_rows_comppls_integer; v_rows_uncomppls_integer; v_compress_rationumber; v_compress_typevarchar2(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才能壓縮出這種比例。