RDBMS/Exadata -- Estimating the HCC compression ratio and the gain //using DBMS_COMPRESSION.GET_COMPRESSION_RATIO

If you are considering buying an Exadata and if you are wondering how much storage space that you can save with the Exadata specific compression methods, you should take a look at this one.

You can see the compression ratio and calculate the % gain for a table by executing the dbms_compression.get_compression_ratio (specifying COMP_FOR_QUERY_HIGH or other compression types such as COMPRESS_FOR_QUERY_LOW as the compression type.)

You can use this method to compute the compression ratio and gain, even if you do not currently have the required hardware to run HCC. (In other words; you don't need your database running on Exadata or you don't need your database files residing on Pillar or ZFS storage hardware)

Note that, the name of compression types change according to the release. For example, it is COMP_QUERY_HIGH if the database is 12C.

Anyways, by using this method, you can have an idea about what you can gain in terms of storage savings.

Ofcourse, there are some requirements for using this method;

• Your database must be 11gR2 ( I recommend >=11.2.0.2, because there are bug records for 11.2.0.1 dbms_compression )
• The RDBMS compatibility parameter must to be set to at least 11.2.0.
• The table to be estimated needs to have a minimum of 1 million rows.

The code reports the stats both from uncompressed and compressed version of the table (the code itself creates those in the scratch tablespace that we specify as the "scratchtbsname")

The code creates 2 internal tables named DBMS_TABCOMP_TEMP_UNCMP (uncompressed) and DBMS_TABCOMP_TEMP_CMP (compressed) . It generates the COMPRESSION_RATIO by comparing these two tables. It drops these tablespace immediately after the estimation.

Let's see an example for QUERY HIGH compression->

• We get a big table such as XLA_DIAG_SOURCES;

SQL> select owner,table_name,num_rows from dba_tables where num_rows>100000 order by num_rows desc;

OWNER                          TABLE_NAME                       NUM_ROWS

------------------------------ ------------------------------ ----------

XLA                            XLA_DIAG_SOURCES                226354744

• The user that owns the table to be analyzed, must have quota on the scratch tablespace. So we quato to the user. This is to avaoid "ORA-01950: no privileges on tablespace" 'XXERM' errors.

SQL>

 ALTER USER XLA QUOTA UNLIMITED ON XXERM;

User altered.    

• We execute the DBMS_COMPRESSION.GET_COMPRESSION_RATIO.

SQL>

set serveroutput on

declare

 v_scratchtbsname varchar2(32) := 'XXERM';

 v_ownname varchar2(32) := 'XLA';

 v_tabname varchar2(32) := 'XLA_DIAG_SOURCES';

 v_partname varchar2(32) := null;

 v_comptype number(10,0) := DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH;

 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(4000);

begin

 DBMS_COMPRESSION.GET_COMPRESSION_RATIO (

 Scratchtbsname => v_scratchtbsname,

 ownname => v_ownname,

 tabname => v_tabname,

 partname => v_partname,

 comptype => v_comptype,

 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('Block count if compressed  :' || v_blkcnt_cmp);

 dbms_output.put_line('Block count if uncompressed: ' || v_blkcnt_uncmp);

 dbms_output.put_line('Row count per block if compressed: ' || v_row_cmp);

 dbms_output.put_line('Row count per block if uncompressed : ' || v_row_uncmp);

 dbms_output.put_line('Compression ratio :' || v_cmp_ratio);

 dbms_output.put_line('Gain (%) :' || round((v_blkcnt_uncmp-v_blkcnt_cmp)/v_blkcnt_uncmp*100,2));

 dbms_output.put_line('Method : ' || v_comptype_str);

end;

OUTPUT

--------------

Compression Advisor self-check validation successful. select count(*) on both

Uncompressed and EHCC Compressed format = 1000001 rows

Block count if compressed: 353

Block count if uncompressed:  19745

Row count if compressed: 2833

Row count if uncompressed : 50

Compression ratio :55.9

Gain (%) :98.21

Method : "Compress For Query High"

Let's see an example for QUERY LOW compression->

SQL> set serveroutput on
declare
v_scratchtbsname varchar2(32) := 'XXERM';
v_ownname varchar2(32) := 'XLA';
v_tabname varchar2(32) := 'XLA_DIAG_SOURCES';
v_partname varchar2(32) := null;
v_comptype number(10,0) := DBMS_COMPRESSION.COMP_FOR_QUERY_LOW;
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(4000);
begin
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
Scratchtbsname => v_scratchtbsname,
ownname => v_ownname,
tabname => v_tabname,
partname => v_partname,
comptype => v_comptype,
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('Block count if compressed  :' || v_blkcnt_cmp);

 dbms_output.put_line('Block count if uncompressed: ' || v_blkcnt_uncmp);

 dbms_output.put_line('Row count per block if compressed: ' || v_row_cmp);

 dbms_output.put_line('Row count per block if uncompressed : ' || v_row_uncmp);

 dbms_output.put_line('Compression ratio :' || v_cmp_ratio);

 dbms_output.put_line('Gain (%) :' || round((v_blkcnt_uncmp-v_blkcnt_cmp)/v_blkcnt_uncmp*100,2));

 dbms_output.put_line('Method : ' || v_comptype_str);

end;

OUTPUT

--------------------

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000001 rows

Block count if compressed: 836

Block count if uncompressed:  19745

Row count if compressed: 1196

Row count if uncompressed : 50

Compression ratio : 23.6

Gain (%) : 95.77

Method : "Compress For Query Low"