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

2022-10-14 12:26:12

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

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

码农公寓

相关文章