one of the nice beauty of compression advisor is that it help to evaluate the compression factor - for HCC - even on non exadata platform.
I am on 12.2 non Exadata systems, but i can quickly check the effect of HCC based compression for critical tables like this:
demo@PDB1> create or replace procedure get_comp_ratios(
2 p_scratchtbsname in varchar2,
3 p_ownname in varchar2,
4 p_objname in varchar2,
5 p_subobjname in varchar2 default null,
6 p_comptype in varchar2 )
7 as
8 l_blkcnt_cmp number;
9 l_blkcnt_uncmp number;
10 l_row_cmp number;
11 l_row_uncmp number;
12 l_cmp_ratio number;
13 l_comptype_str varchar2(80);
14 l_comptype number;
15 begin
16 l_comptype := case when p_comptype = 'QUERY_HIGH' then dbms_compression.COMP_QUERY_HIGH
17 when p_comptype = 'QUERY_LOW' then dbms_compression.COMP_QUERY_LOW
18 when p_comptype = 'ARCHIVE_HIGH' then dbms_compression.COMP_ARCHIVE_HIGH
19 when p_comptype = 'ARCHIVE_LOW' then dbms_compression.COMP_ARCHIVE_LOW end;
20
21 dbms_compression.get_compression_ratio(
22 scratchtbsname => p_scratchtbsname,
23 ownname => p_ownname,
24 objname => p_objname,
25 subobjname => p_subobjname,
26 comptype => l_comptype,
27 blkcnt_cmp => l_blkcnt_cmp,
28 blkcnt_uncmp => l_blkcnt_uncmp,
29 row_cmp => l_row_cmp,
30 row_uncmp => l_row_uncmp,
31 cmp_ratio => l_cmp_ratio,
32 comptype_str => l_comptype_str );
33 dbms_output.put_line( 'Estimated compression ratio using '|| l_comptype_str ||' : ' ||
34 round( l_cmp_ratio, 3) );
35 end;
36 /
Procedure created.
demo@PDB1> select count(*) from stage;
COUNT(*)
----------
60817
demo@PDB1> begin
2 get_comp_ratios(
3 p_scratchtbsname=> 'TS_DATA',
4 p_ownname=> user,
5 p_objname =>'BIG_TABLE',
6 p_comptype => 'QUERY_LOW' );
7 end;
8 /
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Estimated compression ratio using "Compress Query Low" : 6.4
PL/SQL procedure successfully completed.