scripts - sp_compression_ratio.sql - 压缩顾问

–预估表或索引的压缩率
–依次输入 表空间，用户，对象，压缩类型，行数 等
–dbms_compression在11g就有，12c添加了对IM对象压缩率的预估
–见文档1589879.1
–2020年2月1日更新

set serveroutput on
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper(‘&ScratchTBS’),
ownname => upper(‘&ownername’),
objname => upper(‘&TableName’),
subobjname => NULL,
comptype => &compression_type_number,
subset_numrows=> &num_rows,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str );
dbms_output.put_line(‘.’);
dbms_output.put_line(‘OUTPUT: ‘);
dbms_output.put_line(‘Estimated Compression Ratio: ‘||to_char(v_cmp_ratio));
dbms_output.put_line(‘Blocks used by compressed sample: ‘||to_char(v_blkcnt_cmp));
dbms_output.put_line(‘Blocks used by uncompressed sample: ‘||to_char(v_blkcnt_uncmp));
dbms_output.put_line(‘Rows in a block in compressed sample: ‘||to_char(v_row_cmp));
dbms_output.put_line(‘Rows in a block in uncompressed sample: ‘||to_char(v_row_uncmp));
end;
/