11g引入了大量compress相關的特性，其中之一便是dbms_compression包；GET_COMPRESSION_RATIO函數可以幫助我們了解壓縮某個表后各種可能的影響。換而言之，這個函數可以讓我們在具體實施表壓縮技術或者測試前，對于壓縮后的效果能有一個基本的印象。該包在11gr2中被首次引入，故而使用之前版本的包括11gr1都無緣得用。其次除OLTP壓縮模式之外的柱形混合壓縮只能在基于Exdata存儲的表空間上實現。使用DBMS_COMPRESSION包獲取的相關壓縮信息是十分準確的，因為在評估過程中Oracle通過實際采樣并建立模型表以盡可能還原逼真的數據。 我們可以通過trace來分析其評估過程中的具體操作，可以分成2步： 1. 建立原表的樣本表，其采樣值基于原表的大小： SQL> create table samp_dss_nation tablespace SCRATCH as select * from dss_nation sample block (50);Table created. 2. 基于采用表建立對應壓縮類型的模型表： SQL> create table model_dss_nation tablespace SCRATCH compress for query high as select * from samp_dss_nation; create table model_dss_nation tablespace SCRATCH compress for query high as select * from samp_dss_nation * ERROR at line 1: ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage 可以看到在實際建立過程中Oracle將拒絕在非Exdata存儲的表空間上建立該類柱形混合壓縮(包括:COMP_FOR_QUERY_HIGH,COMP_FOR_QUERY_LOW,COMP_FOR_ARCHIVE_HIGH,CO MP_FOR_ARCHIVE_LOW)。但DBMS_COMPRESSION在進行評估時可以繞過Oracle對于該類操作的LOCK. 要在沒有Exdata存儲設備的情況下使用dbms_compression包評測OLTP壓縮模式外的柱狀混合壓縮模式時 (hybrid columnar compression is only supported in tablespaces residing on Exadata storage)，首先需要打上patch 8896202: [oracle@rh2 admin]$ /s01/dbhome_1/OPatch/opatch lsinventory Invoking OPatch 11.1.0.6.6Oracle Interim Patch Installer version 11.1.0.6.6 Copyright (c) 2009, Oracle Corporation. All rights reserved.Oracle Home : /s01/dbhome_1 Central Inventory : /s01/oraInventory from : /etc/oraInst.loc OPatch version : 11.1.0.6.6 OUI version : 11.2.0.1.0 OUI location : /s01/dbhome_1/oui Log file location : /s01/dbhome_1/cfgtoollogs/opatch/opatch2010-06-02_23-08-33PM.logPatch history file: /s01/dbhome_1/cfgtoollogs/opatch/opatch_history.txtLsinventory Output file location : /s01/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2010-06-02_23-08-33PM.txt-------------------------------------------------------------------------------- Installed Top-level Products (1):Oracle Database 11g 11.2.0.1.0 There are 1 products installed in this Oracle Home.Interim patches (1) :Patch 8896202 : applied on Wed Jun 02 21:55:44 CST 2010 Unique Patch ID: 11909460 Created on 29 Oct 2009, 15:21:45 hrs US/Pacific Bugs fixed: 8896202該patch用以：ENABLE COMPRESSION ADVISOR TO ESTIMATE EXADATA HCC COMPRESSION RATIOS 接著我們還需要運行被修改后的DBMSCOMP包創建SQL,具體操作為： SQL> @?/rdbms/admin/prvtcmpr.plbPackage created.Grant succeeded.Package body created.No errors.Package body created.No errors.Type body created.No errors. SQL> @?/rdbms/admin/dbmscomp.sqlPackage created.Synonym created.Grant succeeded.No errors.DBMS_COMPRESSION包在對表壓縮進行評估時，默認表最少數據為1000000行，可能在你的測試庫中沒有這么多數據，我們可以修改這個下限；通過將COMP_RATIO_MINROWS常數修改為1后，就可以分析最小為1行的表了：SQL>create or replace package sys.dbms_compression authid current_user isCOMP_NOCOMPRESS CONSTANT NUMBER := 1;COMP_FOR_OLTP CONSTANT NUMBER := 2;COMP_FOR_QUERY_HIGH CONSTANT NUMBER := 4;COMP_FOR_QUERY_LOW CONSTANT NUMBER := 8;COMP_FOR_ARCHIVE_HIGH CONSTANT NUMBER := 16;COMP_FOR_ARCHIVE_LOW CONSTANT NUMBER := 32;COMP_RATIO_MINROWS CONSTANT NUMBER := 10;COMP_RATIO_ALLROWS CONSTANT NUMBER := -1;PROCEDURE get_compression_ratio(scratchtbsname IN varchar2,ownname IN varchar2,tabname IN varchar2,partname IN varchar2,comptype IN number,blkcnt_cmp OUT PLS_INTEGER,blkcnt_uncmp OUT PLS_INTEGER,row_cmp OUT PLS_INTEGER,row_uncmp OUT PLS_INTEGER,cmp_ratio OUT NUMBER,comptype_str OUT varchar2,subset_numrows IN number DEFAULT COMP_RATIO_MINROWS);function get_compression_type(ownname IN varchar2,tabname IN varchar2,row_id IN rowid) return number;PROCEDURE incremental_compress(ownname IN dba_objects.owner%type,tabname IN dba_objects.object_name%type,partname IN dba_objects.subobject_name%type,colname IN varchar2,dump_on IN number default 0,autocompress_on IN number default 0,where_clause IN varchar2 default '');end dbms_compression;Package created.SQL> alter package dbms_compression compile body;Package body altered.接下來我們通過建立一個基于TPC-D的測試的Schema,保證各表上有較多的數據，并且數據有一定的擬真度：SQL> select table_name,num_rows,blocks from user_tables ;TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- DSS_SUPPLIER 20000 496 DSS_PART 400000 7552 DSS_REGION 5 5 DSS_PARTSUPP 1600000 29349 DSS_LINEITEM 12000000 221376 DSS_ORDER 3000000 48601 DSS_CUSTOMER 300000 6922 DSS_NATION 25 5現在可以進行壓縮評估了，我們針對測試模型Schema編輯以下匿名塊并運行SQL> set serveroutput on; SQL> declarecmp_blk_cnt binary_integer;uncmp_blk_cnt binary_integer;cmp_rows binary_integer;uncmp_rows binary_integer;cmp_ratio number;cmp_typ varchar2(100); BEGINfor i in (SELECT TABLE_NAMEfrom dba_tableswhere compression = 'DISABLED'and owner = 'MACLEAN' and num_rows>1000000) loopfor j in 1 .. 5 loopdbms_compression.get_compression_ratio(scratchtbsname => 'SCRATCH',ownname => 'MACLEAN',tabname => i.table_name,partname => NULL,comptype => power(2, j),blkcnt_cmp => cmp_blk_cnt,blkcnt_uncmp => uncmp_blk_cnt,row_cmp => cmp_rows,row_uncmp => uncmp_rows,cmp_ratio => cmp_ratio,comptype_str => cmp_typ);dbms_output.put_line(i.table_name || '--' || 'compress_type is ' ||cmp_typ || ' ratio :' ||to_char(cmp_ratio, '99.9') || '%');end loop;end loop; end; / DSS_ORDER--compress_type is "Compress For OLTP" ratio : 1.1% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_ORDER--compress_type is "Compress For Query High" ratio : 2.7% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_ORDER--compress_type is "Compress For Query Low" ratio : 1.7% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_ORDER--compress_type is "Compress For Archive High" ratio : 2.9% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_ORDER--compress_type is "Compress For Archive Low" ratio : 2.7% DSS_PARTSUPP--compress_type is "Compress For OLTP" ratio : .9% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_PARTSUPP--compress_type is "Compress For Query High" ratio : 1.8% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_PARTSUPP--compress_type is "Compress For Query Low" ratio : 1.2% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_PARTSUPP--compress_type is "Compress For Archive High" ratio : 1.9% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_PARTSUPP--compress_type is "Compress For Archive Low" ratio : 1.8% DSS_LINEITEM--compress_type is "Compress For OLTP" ratio : 1.4% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_LINEITEM--compress_type is "Compress For Query High" ratio : 3.5% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_LINEITEM--compress_type is "Compress For Query Low" ratio : 2.3% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_LINEITEM--compress_type is "Compress For Archive High" ratio : 4.3% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows DSS_LINEITEM--compress_type is "Compress For Archive Low" ratio : 3.7%PL/SQL procedure successfully completed. 可以從上述測試看到,"Compress For Archive High"壓縮率最高，該類型最適合于數據歸檔存儲，但其算法復雜度高于"Compress For Archive Low",壓縮耗時亦隨之上升。 總體壓縮率都較低，這同TPC-D測試的數據建模有一定關聯，我們再使用一組TPC-H的測試數據來模擬壓縮: SQL> conn liu/liu; Connected.SQL> select num_rows,blocks,table_name from user_tables;NUM_ROWS BLOCKS TABLE_NAME ---------- ---------- ------------------------------3000000 46817 H_ORDER300000 6040 H_CUSTOMER12000000 221376 H_LINEITEM25 5 H_NATION400000 7552 H_PART5 5 H_REGION1600000 17491 H_PARTSUPP20000 496 H_SUPPLIER8 rows selected.SQL> set serveroutput on; SQL> declarecmp_blk_cnt binary_integer;uncmp_blk_cnt binary_integer;cmp_rows binary_integer;uncmp_rows binary_integer;cmp_ratio number;cmp_typ varchar2(100); BEGINfor i in (SELECT TABLE_NAMEfrom dba_tableswhere compression = 'DISABLED'and owner = 'LIU' and num_rows>1000000) loopfor j in 1 .. 5 loopdbms_compression.get_compression_ratio(scratchtbsname => 'SCRATCH',ownname => 'LIU',tabname => i.table_name,partname => NULL,comptype => power(2, j),blkcnt_cmp => cmp_blk_cnt,blkcnt_uncmp => uncmp_blk_cnt,row_cmp => cmp_rows,row_uncmp => uncmp_rows,cmp_ratio => cmp_ratio,comptype_str => cmp_typ);dbms_output.put_line(i.table_name || '--' || 'compress_type is ' ||cmp_typ || ' ratio :' ||to_char(cmp_ratio, '99.9') || '%');end loop;end loop; end; / H_ORDER--compress_type is "Compress For OLTP" ratio : 1.1% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_ORDER--compress_type is "Compress For Query High" ratio : 5.2% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_ORDER--compress_type is "Compress For Query Low" ratio : 2.9% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_ORDER--compress_type is "Compress For Archive High" ratio : 7.2% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_ORDER--compress_type is "Compress For Archive Low" ratio : 5.5% H_PARTSUPP--compress_type is "Compress For OLTP" ratio : .9% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_PARTSUPP--compress_type is "Compress For Query High" ratio : 5.1% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_PARTSUPP--compress_type is "Compress For Query Low" ratio : 2.7% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_PARTSUPP--compress_type is "Compress For Archive High" ratio : 7.2% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_PARTSUPP--compress_type is "Compress For Archive Low" ratio : 5.3% H_LINEITEM--compress_type is "Compress For OLTP" ratio : 1.4% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_LINEITEM--compress_type is "Compress For Query High" ratio : 5.2% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_LINEITEM--compress_type is "Compress For Query Low" ratio : 3.0% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_LINEITEM--compress_type is "Compress For Archive High" ratio : 7.4% Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows H_LINEITEM--compress_type is "Compress For Archive Low" ratio : 5.6%PL/SQL procedure successfully completed. 可以看到相比TPC-D的測試用數據，TPC-H建立的數據更具可壓縮性。 PS: TPC-D represents a broad range of decision support (DS) applications that require complex, long running queries against large complex data structures. Real-world business questions were written against this model, resulting in 17 complex queries. The TPC Benchmark?H (TPC-H) is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions. The performance metric reported by TPC-H is called the TPC-H Composite Query-per-Hour Performance Metric (QphH@Size), and reflects multiple aspects of the capability of the system to process queries. These aspects include the selected database size against which the queries are executed, the query processing power when queries are submitted by a single stream, and the query throughput when queries are submitted by multiple concurrent users. The TPC-H Price/Performance metric is expressed as $/QphH@Size.

轉載于:https://www.cnblogs.com/macleanoracle/archive/2010/06/02/2967423.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的11g compression 新特性(1)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。