[ORACLE] Oracle Compression Advisor dbms_compression.get_compression_ratio压缩比例评估

Oracle Compression Advisor (DBMS_COMPRESSION) 已得到增强，可支持In-Memory 压缩。

该顾问程序估算使用MEMCOMPRESS 可实现的压缩率。这种估算基于表数据样本分析，并能很好地估算表填充到IM 列存储后获得的实际结果。

Constants - Compression Types

Constant	Type	Value	Description
COMP_NOCOMPRESS	NUMBER	1	No compression
COMP_ADVANCED	NUMBER	2	Advanced row compression
COMP_QUERY_HIGH	NUMBER	4	High for query warehouse compression (Hybrid Columnar Compression)
COMP_QUERY_LOW	NUMBER	8	Low for query warehouse compression (Hybrid Columnar Compression)
COMP_ARCHIVE_HIGH	NUMBER	16	High archive compression (Hybrid Columnar Compression)
COMP_ARCHIVE_LOW	NUMBER	32	Low archive compression (Hybrid Columnar Compression)
COMP_BLOCK	NUMBER	64	Compressed block
COMP_LOB_HIGH	NUMBER	128	High compression level for LOB operations
COMP_LOB_MEDIUM	NUMBER	256	Medium compression level for LOB operations
COMP_LOB_LOW	NUMBER	512	Low compression level for LOB operations
COMP_INDEX_ADVANCED_HIGH	NUMBER	1024	High compression level for indexes
COMP_INDEX_ADVANCED_LOW	NUMBER	2048	Low compression level for indexes
COMP_RATIO_LOB_MINROWS	NUMBER	1000	Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated
COMP_BASIC	NUMBER	4096	Basic table compression
COMP_RATIO_LOB_MAXROWS	NUMBER	5000	Maximum number of LOBs used to compute the LOB compression ratio
COMP_INMEMORY_NOCOMPRESS	NUMBER	8192	In-Memory with no compression
COMP_INMEMORY_DML	NUMBER	16384	In-Memory compression level for DML
COMP_INMEMORY_QUERY_LOW	NUMBER	32768	In-Memory compression level optimized for query performance
COMP_INMEMORY_QUERY_HIGH	NUMBER	65536	In-Memory compression level optimized on query performance as well as space saving
COMP_INMEMORY_CAPACITY_LOW	NUMBER	131072	In-Memory low compression level optimizing for capacity
COMP_INMEMORY_CAPACITY_HIGH	NUMBER	262144	In-Memory high compression level optimizing for capacity
COMP_RATIO_MINROWS	NUMBER	1000000	Minimum required number of rows in the object for which HCC ratio is to be estimated
COMP_RATIO_ALLROWS	NUMBER	-1	To indicate the use of all the rows in the object to estimate HCC ratio
OBJTYPE_TABLE	PLS_INTEGER	1	Identifies the object whose compression ratio is estimated as of type table
OBJTYPE_INDEX	PLS_INTEGER	2	Identifies the object whose compression ratio is estimated as of type index

GET_COMPRESSION_RATIO Procedure Parameters

Parameter	Description
scratchtbsname	Temporary scratch tablespace that can be used for analysis
ownname / tabowner	Schema of the table to analyze
tabname	Name of the table to analyze
objname	Name of the object
subobjname	Name of the partition or sub-partition of the object
comptype	Compression types for which analysis should be performed When the object is an index, only the following compression types are valid: COMP_INDEX_ADVANCED_HIGH (value 1024) and COMP_INDEX_ADVANCED_LOW (value 2048). Note: The following compression types cannot be specified in this parameter for any type of object: COMP_BLOCK (value 64) and COMP_BASIC (value 4096).
blkcnt_cmp	Number of blocks used by compressed sample of the table
blkcnt_uncmp	Number of blocks used by uncompressed sample of the table
row_cmp	Number of rows in a block in compressed sample of the table
row_uncmp	Number of rows in a block in uncompressed sample of the table
cmp_ratio	Compression ratio, blkcnt_uncmp divided by blkcnt_cmp
comptype_str	String describing the compression type
subset_numrows	Number of rows sampled to estimate compression ratio.
objtype	Type of the object, either OBJTYPE_TABLE or OBJTYPE_INDEX
lobname	Name of the LOB column
partname	In case of partitioned tables, the related partition name
lobcnt	Number of lobs actually sampled to estimate compression ratio
index_cr	List of indexes and their estimated compression ratios

set serveroutput on

declare
  v_blocks_comp   pls_integer;
  v_blocks_uncomp   pls_integer;
  v_rows_comp   pls_integer;
  v_rows_uncomp   pls_integer;
  v_compress_ratio  number;
  v_compress_type   varchar2(32767);
begin
  DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'TEST',
   ownname =>'C##SAPR3',
   objname =>'TEST',
   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;
/

结果

Message: PL/SQL procedure successfully completed
Console Output Begins: 
output: 
estimated compression ratio: 2.2
blocks used - compressed sample: 303
blocks used - uncompressed sample: 696
rows in a block - compressed sample: 401
rows in a block - uncompressed sample: 174
Console Output Ends.

reference:https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_COMPRESSION.html#GUID-29865B67-68D5-406B-A95D-423545CDB13A