Hybrid Columnar Compression ( HCC ) in Oracle Exadata -2

Hi,

I will explain Hybrid Columnar compression ( HCC ) feature in Oracle Exadata in this article.

 

Now let’s see HCC Compression with a real-time test. First of all, I create a table for testing purposes and insert about 160 million data.

 

create table msdeveci.hcc_test as
select
rownum as id,
rownum + 1 as id2,
'Mehmet Salih Deveci Exadata Egitimi HCC Testleri' as name,
mod(rownum,5) as id3,
mod(rownum,10) as id4 ,
14531453 as fiyat,
trunc(sysdate - 100 + mod(rownum,10000))
as tarih,
trunc(sysdate - 9999 + mod(rownum,10000))
as tarih2
from dual connect by level<=1e7;

 

 

From this test table, we will see the performance, compression and storage ratio by compressing it with Classic, OLTP and Exadata HCC methods respectively.

 

 

Exadata or Non Exadata in the environment of Oracle databases, we can compress the table with the following classic compression method.

160 million rows test table is inserted in 8:15 minutes into Classic compressed table.

 

 

With OLTP Compression method, compress as follows and perform the same insert operation.

160 million rows test table is inserted in 22:52minutes into OLTP compressed table.

 

 

Now let’s do the HCC method with Exadata. First let’s compress this with Query Low as follows.

 

 

160 million rows test table is inserted in 07:12 minutes into Query Low compressed table.

 

 

You can compress with Query High method like following.

 

 

 

160 million rows test table is inserted in 07:56minutes into Query High compressed table.

 

You can compress with Archive Low method like following.

160 million rows test table is inserted in 08:16 minutes into Archive Low  compressed table.

 

 

You can compress with Archive High method like following.

160 million rows test table is inserted in 31:57 minutes into Archive High compressed table.

 

 

 

Let’s look at the segment size of these tables like following.

 

Although the Query High method seems to be compressed as much as Archive, it will make a difference in very large tables or different types of tables. The Archive method does 2-3 times more compression than the Query method, but is not as efficient as the Query method.

 

 

As a result, we have seen that when we compress a 16GB table with a classic method, it has compressed to 5500MB, which is about 3 times the compression.

 

However, when we used Exadata’s HCC, we saw that Query Low compressed up to 2500MB, which is about 6 times compression. At Query High, 16GB table is compressed to 500MB, which means an average of 32 times compression.

 

Now lets go to test for big table to see compression and performance clearly. Perform same compression like above.

 

 

Table CreateTable Create2

 

 

Classic

 

Query Low

 

Query High

 

Archive low

 

 

Archive High

 

All compression method are done like above. And compressed tables size are like following.

 

Sonuç2

 

 

According to the results above, Archive High has compressed up to120 times, and Archive low has compressed up to 80 times. Compression details and OLTP times made to these tables can be found above.

 

As a result, If you want more performance during compression, choose HCC Query Low and Query High method. If you want more compression and storage saving during compression, then choose Archive High and Archive low method.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.