How to estimate the effect of table compression in Oracle

Before using compression, we can estimate how much the effect of using compression can be.

11GR2 can use dbms_comp_advisor before, the specific code has been given in the attachment. You only need to execute two files Dbmscomp.sql and PRVTCOMP.PLB, and then use the Dbms_comp_advisor.getratio stored procedure. No longer described in detail.

Sql> set Serveroutput
on sql> execdbms_comp_advisor.getratio (' SH ', ' SALES ', ten)    
sampling table:sh. SALES    
sampling percentage:10%    
estimated compression ratio for the advancedcompression option is:2.96

11gr2 later, the system will bring a dbms_compression package, instead of dbms_comp_advisor to provide services.

_sys@fake> desc dbms_compression PROCEDURE get_compression_ratio argumentname Type    
 In/out Default?                -------------------------------------------------------------------Scratchtbsname VARCHAR2                In Ownname VARCHAR2 in TabName VARCHAR2                  In PartName VARCHAR2 in Comptype number 
 In blkcnt_cmp Binary_integer out blkcnt_uncmp Binary_integer out
 row_cmp Binary_integer out row_uncmp Binary_integer out Cmp_ratio number out Comptype_str VARCHAR2 out SU   Bset_numrows number in DEFAULT FUNCTION get_compression_type RETURNS number 
 ArgumentName Type in/out Default?                -------------------------------------------------------------------Ownname VARCHAR2                   In TabName VARCHAR2 in row_id ROWID    
 In PROCEDURE incremental_compress argumentname Type in/out Default?            -------------------------------------------------------------------Ownname VARCHAR2 (30)            In TabName VARCHAR2 (128) in PartName VARCHAR2 (30)                  In ColName VARCHAR2 in dump_on number In default autocompress_on number in default Where_clause Varc HAR2 in DEFAULT

Focus on Get_compression_ratio This stored procedure, which can estimate the compression ratio of the table.

You can use the following anonymous block execution.

DECLARE Blkcnt_comp Pls_integer;    
 BLKCNT_UNCM Pls_integer;    
 Row_comp Pls_integer;    
 ROW_UNCM Pls_integer;    
 Comp_ratio number;    
 Comp_type VARCHAR2 (30);    
 Username varchar2: = ' &user ';    
TableName VARCHAR2: = ' &TB ';    
                                        BEGIN dbms_compression.get_compression_ratio (' &usedtbs ', username,    
                                        TableName, NULL, Dbms_compression. COMP_FOR_OLTP, Blkcnt_comp, BLKCN    
                                        T_UNCM, Row_comp, ROW_UNCM,    
 Comp_ratio, Comp_type); Dbms_output.put_line (' Sampling table: ' | | username| | '. ' | |    
 TableName); dbms_output.put_lINE (' estimated compression ratio: ' | |    
 To_char (Comp_ratio));    
Dbms_output.put_line (' Compression Type: ' | | | comp_type ');    
End; /

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Execution effect:

/    
Enter value for User:dexter    
old  8:   username varchar2 (): = ' &user ';    
New  8:   username varchar2: = ' DEXTER ';    
Enter value for Tb:account    
old  9:   tablename varchar2: = ' &TB ';    
New  9:   tablename VARCHAR2: = ' account ';    
Enter value for usedtbs:users old    
:   dbms_compression.get_compression_ratio (' &usedtbs ',    
new 11:  dbms_compression.get_compression_ratio (' USERS ',    
sampling table:dexter. Account    
estimated compression ratio:1    
compression Type: ' Compress for OLTP '
         
pl/sql procedure Successfully completed.

Because there are very few duplicate values in the table, estimated compression ratio:1 above indicates that there is no compression effect.

Advanced compression, based on compression within the block. So even if there is a duplicate value, but not in a block, then the advanced compression does not work.

Here we highlight a parameter comptype, which is a total of 6 options, respectively

Comp_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: =;    
Comp_for_archive_low          CONSTANT Number: = 32;

Query high Below is the content of HCC (Hybridcolumnar Compression) because it is associated with exadata storage nodes, so it is not available in a non-exadata integrated environment. Interestingly, you can use Get_compression_ratio to estimate the percentage of compression in a normal environment.

11GR2 previous compression-advisor stored procedure download address:

http://download.csdn.net/detail/renfengjun/7514723