How to estimate the effect of table compression in Oracle

Source: Internet
Author: User
Tags constant

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.