Before using compression, we can estimate the effect of compression.
Dbms_comp_advisor can be used before 11gr2. The specific code is provided 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. It is not described in detail.
SQL> set serveroutput onSQL> execdbms_comp_advisor.getratio('SH','SALES',10)Sampling table: SH.SALESSampling percentage: 10%Estimated compression ratio for the advancedcompression option is : 2.96
After 11gr2, the system will bring a dbms_compression package to provide services in place of dbms_comp_advisor.
_sys@FAKE> desc dbms_compressionPROCEDURE 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 SUBSET_NUMROWS NUMBER IN DEFAULTFUNCTION GET_COMPRESSION_TYPE RETURNS NUMBER ArgumentName Type In/Out Default? ----------------------------------------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN ROW_ID ROWID INPROCEDURE 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 VARCHAR2 IN DEFAULT
Focus on the Stored Procedure GET_COMPRESSION_RATIO, which can estimate the compression ratio of the table.
You can use the following anonymous blocks for 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(30) := '&USER'; tablename varchar2(30) := '&TB' ;BEGIN dbms_compression.get_compression_ratio('&Usedtbs', username, tablename, NULL, dbms_compression.COMP_FOR_OLTP, blkcnt_comp, blkcnt_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;/
Execution result:
/Enter value for user: DEXTERold 8: username varchar2(30) :='&USER';new 8: username varchar2(30) :='DEXTER';Enter value for tb: ACCOUNTold 9: tablename varchar2(30) :='&TB' ;new 9: tablename varchar2(30) :='ACCOUNT' ;Enter value for usedtbs: USERSold 11: dbms_compression.get_compression_ratio('&Usedtbs',new 11: dbms_compression.get_compression_ratio('USERS',Sampling table: DEXTER.ACCOUNTEstimated compression ratio: 1Compression Type: "Compress For OLTP" PL/SQL procedure successfully completed.
Because there are very few duplicate values in the table,In the previous article, Estimated compression ratio: 1, indicating that there is no compression effect.
Advanced compression, based on intra-block compression. So even if there is a duplicate value but it is not in a block, the advanced compression still cannot work.
Here we will focus on a parameter COMPTYPE, which has six options:
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 := 16;COMP_FOR_ARCHIVE_LOW CONSTANT NUMBER := 32;
The following are the contents of HybridColumnar Compression in Query high. Because it is related to the storage node of Exadata, it cannot be used in a non-Exadata all-in-one environment. However, you can use get_compression_ratio in a common environment to estimate the compression ratio.
Compression-advisor stored procedure before 11gr2:
Http://download.csdn.net/detail/renfengjun/7514723