Exadata - HCC for Exadata Servers (HCC)

HCC for Exadata servers ( HCC)

What is HCC?

Hybrid Columnar Compression on Exadata enables the highest levels of data compression and provides enterprises with tremendous cost-savings and performance improvements due to reduced I/O. HCC is optimized to use both database and storage capabilities on Exadata to deliver tremendous space savings AND revolutionary performance. Average storage savings can range from 10x to 15x depending on which Hybrid Columnar Compression level is implemented – real world customer benchmarks have resulted in storage savings of up to 204x

HCC Technology overview

Oracle’s Hybrid Columnar Compression technology is a new method for organizing data within a database block. As the name implies, this technology utilizes a combination of both row and columnar methods for storing data. This hybrid approach achieves the compression benefits of columnar storage, while avoiding the performance shortfalls of a pure columnar format. 

A logical construct called the compression unit is used to store a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together and compressed. After the column data for a set of rows has been compressed, it is stored in a compression unit.

4 different type of HCC available are 

Query low
Query high
Archive low
Archive High

Please run reports for all 4 type of compression using script mentioned on TECH_STEP1 by mentioned any one of the below compression which will give you compression ratio.

Query low 
Query high 
Archive low 
Archive High


Technical details on gather statistic ratio and enabling HCC

TECH_STEP1: compression_stats

Compression_Stats_Script for TRANSACTIONS_STG table which will actual tells you how much compression it is going to give you.

TRANSACTIONS_STG_Stats_Query_Low.sql

spool TRANSACTIONS_STG_Query_Low.log
select name from v$database ;
set serveroutput on;
set time on
set timing on
select to_char(sysdate,'DD-MM-YYYY:HH24:MM:SS') time_now from dual;
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   => 'XXLGARCH',                          -- Tablespace Name
 ownname          => 'XXLGARCH',                          -- USER NAME
 tabname          => 'TRANSACTIONS_STG',          -- TABLE NAME
 partname         => NULL,
 comptype         => dbms_compression.comp_for_query_low, --compression type
 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('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));
end;
/
select to_char(sysdate,'DD-MM-YYYY:HH24:MM:SS') time_now from dual;
spool off;
exit;

TECH_STEP2: compression_stats_script log

Below is the log collected from the above compression stat script. Which will give you compression prediction ration

XLG_AED_TRANSACTIONS_STG_Query_Low.log 

NAME                                                                            
---------                                                                       
TESTDB                                                                         


TIME_NOW                                                                        
-------------------                                                             
13-12-2017:01:12:16                                                             

Elapsed: 00:00:00.00
Compression Advisor self-check validation successful. select count(*) on both   
Uncompressed and EHCC Compressed format = 1000001 rows                          
Estimated Compression Ratio: 5.5 ----------- Compression Ratio                                    
Blocks used by compressed sample: 23527                                         
Blocks used by uncompressed sample: 130140                                      

PL/SQL procedure successfully completed.

Elapsed: 00:12:50.58

TIME_NOW                                                                        
-------------------                                                             
13-12-2017:01:12:07                                                             

Elapsed: 00:00:00.00


TECH_STEP3: Actual compression_scripts

Actual compression_scripts for TRANSACTIONS_STG table, below is the script which will do actual compression on the table.

TRANSACTIONS_STG_Compress_archive_low.sql 

set serveroutput on;
spool TRANSACTIONS_STG_Compress_archive_low.log
set timing on

select name from v$database;

col SEGMENT_NAME format a40
select segment_name, round(sum(bytes)/1024/1024/1024,2) total_space_GB  from dba_segments  where  owner='XXLGARCH' and segment_name='TRANSACTIONS_STG' 
group by segment_name;

select to_char(sysdate,'DD-MM-YYYY:HH:MI:SS') from dual;

alter table XXLGARCH.TRANSACTIONS_STG move compress for archive low;

select to_char(sysdate,'DD-MM-YYYY:HH:MI:SS') from dual;

col SEGMENT_NAME format a40
select segment_name, round(sum(bytes)/1024/1024/1024,2) total_space_GB  from dba_segments  where  owner='XXLGARCH' and segment_name='TRANSACTIONS_STG' 
group by segment_name;

set lines 300
select owner,table_name,index_name from dba_indexes where status='UNUSABLE';

spool off
exit;

Regards,

Mallik